home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam    

Web Database Applications with PHP \& MySQLWeb Database Applications with PHP \& MySQLSearch this book

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



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.







??????????????@Mail.ru