only for RuBoard - do not distribute or recompile Previous Section Next Section

D.3 MySQL Session Store

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

D.3.1 Session Table Structure

For the session handler code that stores session variables, a table is needed to hold sessions. 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:

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)
);

There is an additional index that allows 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:

mysql> SELECT * FROM PHPSESSION;
+------------------------------+--------------------------------+----------------+
| session_id                   | session_variable               | last_updated   |
+------------------------------+--------------------------------+----------------+
| d003a284fbbf982c90aade5485   | count|i:39;start|i:1000900585; | 1000900661.575 |
| b74e720d5395800d5fabe7eab8   | count|i:0;start|i:1000900677;  | 1000900678.705 |
+------------------------------+--------------------------------+----------------+
2 rows in set (0.02 sec)

D.3.2 Handler Implementations

The best way to arrange the functions that implement the session handlers is to place them in a single support file. By placing the functions shown in Example D-2 through Example D-9 in the one file, you can include that file at the beginning of any PHP script using sessions. The support file containing the handler implementations—for example mysql_sessions.inc—must be included before any session calls are made as shown in the following example:

<?php
  include("mysql_sessions.inc");
  start_session(  );

  //... rest of script ...

?>
D.3.2.1 Support functions

The MySQL-based session handlers use the showerror( ) function implemented in the error.inc include file, and the $hostName, $username, and $password variables set in the db.inc include file. The showerror( ) function is used by the handler implementations to display details about MySQL errors. The db.inc file provides a central location for maintaining connection details. The error.inc and db.inc files are described in Chapter 4.

Example D-2 shows the function getMicroTime( ), which generates a timestamp. The timestamp records the last session access in 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 D-2. The support function getMicroTime( )
include("error.inc");
include("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]);
}  
D.3.2.2 sessionOpen

Example D-3 shows the first of the session handlers required by PHP session management. The sessionOpen( ) function sets two global variables to hold the database connection and the table that manages the session variables. PHP passes the php.ini file values of session.save_path and session.name as $database_name and $table_name, respectively. The $database_name parameter selects the database, and the $table_name parameter is stored in the global variable $session_table. The global variables $session_table and $connection formulate and execute SELECT, INSERT, UPDATE, and DELETE queries in the other handlers.

Example D-3. The sessionOpen handler
// The database connection
$connection;
  
// The global variable that holds the table name
$session_table;

// The session open handler called by PHP whenever
// a session is initialized. Always returns true.

function sessionOpen($database_name, $table_name)
{

  // Save the database name in a global variable
  global $connection;
  global $hostName;
  global $username;
  global $password;

  if (!($connection = @ mysql_pconnect($hostName, 
                                       $username, 
                                       $password)))
     showerror(  );

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

  // Save the table name in a global variable
  global $session_table;
  $session_table = $table_name;

  return true;
}

Using the values of session.save_path and session.name as the database name and the table name respectively, the MySQL session handlers developed in this appendix can be configured to use any database and table as a session store. With the handler shown in Example D-3, the name of the table is the same as the name of the cookie used to hold the session ID. For example, consider the following php.ini file settings:

session.save_path = winestore
session.name = PHPSESSION

With these settings, our module uses the PHPSESSION table in the winestore database.

D.3.2.3 sessionRead

The sessionRead( ) handler function—shown in Example D-4—is called by PHP each time a session is initialized. The handler returns the serialized string that holds the session variables for the given 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. If an error occurs when the SELECT query is executed, showerror( ) is called.

The query is constructed using the global variables $session_table and executed using the global variable $connection set up by the sessionOpen( ) handler. Note that this function returns all the session variables in the one serialized string. The calling PHP code converts the string to the individual session variables and sets up the $HTTP_SESSION_VARS array and the associated global variables if register_globals has been enabled.

Example D-4. The sessionRead handler
// This function is called whenever a session_start(  )
// call is made and reads the session variables
// Returns "" when a session is not found
//         (serialized)string - session exists

function sessionRead($sess_id)
{
  // Access the DBMS connection
  global $connection;
   
  // Access the global variable that holds the name
  // of the table that holds the session variables
  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"];
  }
}
D.3.2.4 sessionWrite

The sessionWrite( ) handler function isn't responsible only for writing variables to the session store but also records when session variables are read. sessionWrite( ) is called by PHP each time a variable is registered, when session variables change, and when a session is initialized. It's important that the last_access time-stamp is updated each time a session is initialized; that is, when session_start( ) is called. 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 D-5 starts by executing a SELECT query to determine if a session exists. If a session is found, then 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( ) that is shown in Example D-2.

Example D-5. The sessionWrite handler
// This function is called when a session is initialized
// with a session_start(  ) call, when variables are
// registered or unregistered, and when session variables
// are modified. Returns true on success.

function sessionWrite($sess_id, $val)
{
  global $connection;
  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(  );
  }
  return true;
}
D.3.2.5 sessionClose

The sessionClose( ) handler can perform any housekeeping functions that need to be executed before a script ends. In the handler implementation shown in Example D-6, the connection setup returned by the sessionOpen( ) is true.

Example D-6. The sessionClose handler
// This function is executed on shutdown of the session. 
// Always returns true.

function sessionClose($sess_id)
{
    return true;
}
D.3.2.6 sessionDestroy

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

Example D-7. 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)
{
  global $connection;
  global $session_table;

  $delete_query = 
    "DELETE FROM $session_table 
      WHERE session_id = '$sess_id'";

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

  return true;
}
D.3.2.7 Garbage collection

The last handler to be defined is the garbage collection function. Example D-8 shows the implementation of sessionGC( ), which queries for all session rows that have been dormant for $max_lifetime seconds. PHP passes the value set in the session.gc_maxlifetime parameter of the php.ini file. 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 D-8. 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)
{
  global $connection;
  global $session_table;

  $time_stamp = getMicroTime(  );
 
  $delete_query =
    "DELETE FROM $session_table
      WHERE last_accessed < ($time_stamp - $max_lifetime)";

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

  return true;
}
D.3.2.8 Registering session handlers

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

Example D-9. 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");

D.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

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

Example D-10. A simple PHP script that uses the MySQL session store
<?php
  // Include the MySQL session handlers
  include("mysql_session.inc");

  // Initialize a session. This call either creates 
  // a new session or re-establishes an existing one.
  session_start(  );

  // If this is a new session, then the variable
  // $count is not registered
  if (!session_is_registered("count")) 
  {
    session_register("count");
    session_register("start");

    $count = 0;
    $start = time(  );
  } 
  else 
  {
    $count++;
  }

  $sessionId = session_id(  );

?>
<!DOCTYPE HTML PUBLIC 
   "-//W3C//DTD HTML 4.0 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd" >
<html>
  <body>
    <p>This page points at a session 
        (<?=$sessionId ?>)
    <br>count = <?=$count ?>.
    <br>start = <?=$start ?>.
    <p>This session has lasted 
      <?php 
        $duration = time(  ) - $start; 
        echo "$duration"; 
      ?> 
      seconds.
  </body>

</html>
only for RuBoard - do not distribute or recompile Previous Section Next Section