F.3 MySQL Session Store

In this section we develop a set of user-defined handlers that store session variables in a MySQL table.

F.3.1 Session Table Structure

For the session handler code that stores session variables, a table is needed to hold sessions. You can create the table as part of your application database (for example, as a table in the winestore database), or create a new database to store only sessions. We follow the former approach, but it doesn't matter what you choose in practice; the database is accessed using its own connection, so you won't have any trouble accessing the table even when other tables are locked.

The following SQL CREATE TABLE statement creates a table to hold the session ID, the serialized session variables, and a timestamp to indicate when the session was last accessed. If you want to add it to the winestore database, login to the MySQL command interpreter as root user and type the following:

mysql> use winestore;

Database changed

mysql> CREATE TABLE PHPSESSION(

    ->   session_id varchar(50) NOT NULL,

    ->   session_variable text,

    ->   last_accessed decimal(15,3) NOT NULL,

    ->   PRIMARY KEY (session_id),

    ->   KEY last_acc (last_accessed)

    -> ) type=MyISAM;

Query OK, 0 rows affected (0.05 sec)

The session_id attribute is the primary key and the last_accessed attribute is indexed to allow fast deletion of dormant sessions using custom garbage-collection code described later.

When the code is up and running, the PHPSESSION table can be examined to see the current sessions. The following example shows that two sessions are held in the table (we have truncated the values in the session_id column to fit on the page):

mysql> SELECT * FROM PHPSESSION;

+---------------------+------------------------------+--------------+

| session_id          | session_variable             | last_updated |

+---------------------+------------------------------+--------------+

| ee83912e13b11a8c042 | count|i:39;start|i:90900585; | 90900661.575 |

| 6e721f8557df77b7b6d | count|i:0;start|i:90900677;  | 90900678.705 |

+---------------------+------------------------------+--------------+

2 rows in set (0.02 sec)

F.3.2 Handler Implementations

We store the session handlers in a support file. By placing the functions shown in Examples Example F-1 through Example F-8 in the one file, you can require that file at the beginning of any PHP script that uses sessions. If the file is saved as mysql_sessions.inc, then it's required as shown in the following example:

<?php

  require "mysql_sessions.inc";

  start_session( );



  //... rest of the script ...

?>

F.3.2.1 Support functions

The MySQL-based session handlers can use the simple showerror( ) function discussed in Chapter 6 or the custom error handler developed in Chapter 12. To keep the examples simple, we use the showerror( ) function that's stored in the db.inc require file. Using this approach, when an error occurs, the function reports a MySQL error message and stops the script. If you use a custom handler instead and replace the calls to showerror( ) with calls to trigger_error( ) , then you can handle errors more gracefully by logging them to a file and presenting an error message to the user that has a look and feel that's consistent with the application.

The db.inc file that contains showerror( ) also maintains the database credentials, stored in the $hostName, $username, and $password variables.

Example F-1 shows the function getMicroTime( ) , which generates a timestamp. The timestamp records the last session access by the sessionWrite( ) handler and creates a query that identifies idle sessions in the sessionGC( ) handler. The sessionWrite( ) handler and the sessionGC( ) handler are developed later in this section.

Example F-1. The support function getMicroTime( )

<?php

require "db.inc";



// Returns current time as a number. Used for recording the

// last session access.



function getMicroTime( )

{

  // microtime( ) returns the number of seconds since

  // 0:00:00 January 1, 1970 GMT as a  microsecond part

  // and a second part. e.g.: 0.08344800 1000952237



  // Convert the two parts into an array

  $mtime = explode(" ", microtime( ));



  // Return the addition of the two parts e.g.: 1000952237.08344800

  return($mtime[1] + $mtime[0]);

}

F.3.2.2 Session open handler

Example F-2 shows the first of the session handlers required by PHP session management. When PHP calls the sessionOpen( ) function, the values of session.save_path and session.name defined in the php.ini file, are passed as parameters and these values can be used however you choose. In the sessionOpen( ) function defined here, these two parameters identify the database and table used to store session variables.

The $database_name parameter—the value of session.save_path—is used to select a database after a connection to a MySQL server is established. The $table_name parameter—the value of session.name—is stored in the global variable $session_table. The global variables $session_table and $connection appear in subsequent session store handlers, where they are used to execute SELECT, INSERT, UPDATE, and DELETE queries.

Example F-2. The sessionOpen( ) handler

// The database connection

$connection = NULL;

  

// The global variable that holds the table name

$session_table = NULL;



// The session open handler called by PHP whenever

// a session is initialized. Always returns true.



function sessionOpen($database_name, $table_name)

{

  // Save the database connection in a global variable

  global $connection;



  // Save the session table name in a global variable

  global $session_table;



  // Database credentials

  global $hostName;

  global $username;

  global $password;



  if (!($connection = @ mysql_connect($hostName, $username, $password)))

     showerror( );



  if (!mysql_select_db($database_name, $connection))

     showerror( );



  $session_table = $table_name;



  return true;

}

With the handler shown in Example F-2, the database and table names can be configured in the php.ini file. For example, with the following php.ini file settings:

session.save_path = winestore

session.name = PHPSESSION

the openSession( ) function selects the winestore database and identifies the PHPSESSION table. If you want to use our session handler, you need to modify your php.ini to have these settings and restart your Apache.

F.3.2.3 Session read handler

The sessionRead( ) handler function shown in Example F-3 is called by PHP to read session variables. The handler returns the serialized string that holds the session variables for the session ID $sess_id. The function executes a query to find the row with a session_id equal to $sess_id and, if the row is found, the session_variable attribute is returned. If no session is found, sessionRead( ) returns a blank string.

The sessionRead( ) handler uses the global variables $session_table and $connection, set up by the sessionOpen( ) handler, to formulate and execute the query. The function returns all the session variables as a serialized string and PHP's session management layer automatically deserializes the string to set up the $_SESSION array; you don't have to worry about serialization and deserialization in your code.

Example F-3. The sessionRead( ) handler

// This function is called whenever a session_start( ) call is

// made and reads the session variables associated with the session

// identified by the $sess_id parameter. Returns "" when a session

// is not found and the session variables as a serialized string

// when the session exists.



function sessionRead($sess_id)

{

  // Access the DBMS connection

  global $connection;



  // Access the sessions table

  global $session_table;



  // Formulate a query to find the session identified by $sess_id

  $search_query = "SELECT * FROM {$session_table}

                   WHERE session_id = '{$sess_id}'";



  // Execute the query

  if (!($result = @ mysql_query($search_query, $connection)))

     showerror( );



  if(mysql_num_rows($result) == 0)

    // No session found - return an empty string

    return "";

  else

  {

    // Found a session - return the serialized string

    $row = mysql_fetch_array($result);

    return $row["session_variable"];

  }

}

F.3.2.4 Session write handler

The sessionWrite( ) handler function is called by PHP at the end of a script that calls session_start( ), and is responsible for writing variables to the session store and for recording when a session was last accessed. It's important that the last_access time-stamp is updated each time the sessionWrite( ) handler is called, even if the session variables haven't changed. If the last access time isn't updated, a session may be seen as dormant by the garbage collection handler and destroyed even though the variables have recently been read.

Example F-4 starts by executing a SELECT query to determine if a session exists. If a session is found, an UPDATE query is executed; otherwise a new session row is created with an INSERT query. Both the INSERT and UPDATE queries set the last_accessed field with the timestamp created by the support function getMicroTime( ) shown in Example F-1.

Example F-4. The sessionWrite( ) handler

function sessionWrite($sess_id, $val)

{

  // Access the DBMS connection

  global $connection;



  // Access the sessions table

  global $session_table;



  $time_stamp = getMicroTime( );



  $search_query = "SELECT session_id FROM {$session_table}

                   WHERE session_id = '{$sess_id}'";



  // Execute the query

  if (!($result = @ mysql_query($search_query, $connection)))

     showerror( );



  if(mysql_num_rows($result) == 0)

  {

     // No session found, insert a new one

     $insert_query = "INSERT INTO {$session_table}

                      (session_id, session_variable, last_accessed)

                      VALUES ('{$sess_id}', '{$val}', {$time_stamp})";



     if (!mysql_query($insert_query, $connection))

        showerror( );

  }

  else

  {

     // Existing session found - Update the session variables

     $update_query = "UPDATE {$session_table}

                      SET session_variable = '{$val}',

                          last_accessed = {$time_stamp}

                      WHERE session_id = '{$sess_id}'";



     if (!mysql_query($update_query, $connection))

        showerror( );

  }

}

F.3.2.5 Session close handler

The sessionClose( ) handler is called by PHP when a session-based script ends, and can be used to perform any housekeeping functions needed to close a session store. The handler implementation shown in Example F-5, accomplishes the minimum, just returning true.

Example F-5. The sessionClose( ) handler

// This function is executed on shutdown of the session. 

// Always returns true.



function sessionClose( )

{

    return true;

}

F.3.2.6 Session destroy handler

When session_destroy( ) is called, PHP calls the sessionDestroy( ) handler shown in Example F-6. This function deletes the row identified by the $sess_id argument from the table that holds the session variables.

Example F-6. The sessionDestroy( ) handler

// This is called whenever the session_destroy( ) function

// call is made. Returns true if the session  has successfully

// been deleted.



function sessionDestroy($sess_id)

{

  // Access the DBMS connection

  global $connection;



  // Access the sessions table

  global $session_table;



  $delete_query = "DELETE FROM {$session_table}

                   WHERE session_id = '{$sess_id}'";



  if (!($result = @ mysql_query($delete_query, $connection)))

     showerror( );



  return true;

}

F.3.2.7 Garbage collection handler

The last handler to be defined is the garbage collection function. Example F-7 shows the implementation of sessionGC( ) , which queries for all session rows that have been dormant for $max_lifetime seconds. When PHP session management calls this function, the value of the session.gc_maxlifetime parameter is passed as $max_lifetime. The time a session has been dormant is calculated by subtracting the last update time held in the session row from the current time.

Example F-7. The sessionGC( ) garbage collection handler

// This function is called on a session's start up with the

// probability specified in session.gc_probability.  Performs

// garbage collection by removing all sessions that haven't been

// updated in the last $max_lifetime seconds as set in

// session.gc_maxlifetime.

// Returns true if the DELETE query succeeded.



function sessionGC($max_lifetime)

{

  // Access the DBMS connection

  global $connection;



  // Access the sessions table

  global $session_table;



  $current_time = getMicroTime( );



  $delete_query = "DELETE FROM {$session_table}

             WHERE last_accessed < ({$current_time} - {$max_lifetime})";



  if (!($result = @ mysql_query($delete_query, $connection)))

     showerror( );



  return true;

}

F.3.2.8 Registering session handlers

Finally, the handlers implemented in Examples Example F-2 through Example Example F-7 need to be registered as callback functions with PHP. Example F-8 shows the call to session_set_save_handler( ) with the names of each handler function.

Example F-8. Registering the user-defined session handlers with PHP

// Call to register user call back functions.



session_set_save_handler("sessionOpen", 

                         "sessionClose", 

                         "sessionRead", 

                         "sessionWrite", 

                         "sessionDestroy", 

                         "sessionGC");

?>

F.3.3 Using the User-Defined Session Handler Code

Once the user-defined session handler code is implemented, it can be used by setting up the session configuration in the php.ini file and including the library at the top of PHP scripts that use sessions. The session.save_handler parameter needs to be set to user, indicating that user-defined handlers are used; the session.save_path parameter is set to the name of the database; and, session.name parameter is set to the name of the table. The following example settings are used if session variables are stored in the PHPSESSION table of the winestore database:

session.save_handler = user

session.save_path = winestore

session.name = PHPSESSION

After changing php.ini, you need to restart Apache for the settings to take effect.

Example F-9 shows how application scripts are modified to use the MySQL session store; the script is a copy of Example 10-1, with the addition of the directive to require mysql_session.inc.

Example F-9. A simple PHP script that uses the MySQL session store

<?php

  require_once "HTML/Template/ITX.php";

  require "mysql_sessions.inc";



  // This call either creates a new session or finds an existing one.

  session_start( );



  // Check if the value for "count" exists in the session store

  // If not, set a value for "count" and "start"

  if (!isset($_SESSION["count"]))

  {

    $_SESSION["count"] = 0;

    $_SESSION["start"] = time( );

  }



  // Increment the count

  $_SESSION["count"]++;



  $template = new HTML_Template_ITX("./templates");

  $template->loadTemplatefile("example.d-10.tpl", true, true);



  $template->setVariable("SESSION", session_id( ));

  $template->setVariable("COUNT", $_SESSION["count"]);

  $template->setVariable("START", $_SESSION["start"]);

  $duration = time( ) - $_SESSION["start"];

  $template->setVariable("DURATION", $duration);



  $template->parseCurrentBlock( );



  $template->show( );

?>

The script uses the template shown in Example F-10.

Example F-10. The template used with Example F-9

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

                      "http://www.w3.org/TR/html401/loose.dtd">

<html>

<head>

  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

  <title>Session State Test</title>

</head>

<body>

    <p>This page points at a session {SESSION}

    <br>count = {COUNT}

    <br>start = {START}

    <p>This session has lasted {DURATION} seconds.

</body>

</html>

F.3.4 PEAR's HTTP_Session Package

The PEAR HTTP_Session module is another example of PHP session handlers that implement a MySQL based session store. We introduce PEAR in Chapter 10.

The module has been developed to define an HTTP_Session class that provides a higher-level API to the PHP session management than the code we've developed here. The HTTP_Session class includes functions that not only access session variables, but also provide control over the session parameters such as cookies. HTTP_Session includes a database container method HTTP_Session::setContainer( ) that sets up PHP to use MySQL session handlers.