In this section we develop a set of user-defined handlers that store session variables in a MySQL table.
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)
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 ... ?>
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.
<?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]); }
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.
// 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.
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.
// 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"]; } }
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.
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( ); } }
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.
// This function is executed on shutdown of the session. // Always returns true. function sessionClose( ) { return true; }
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.
// 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; }
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.
// 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; }
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.
// Call to register user call back functions. session_set_save_handler("sessionOpen", "sessionClose", "sessionRead", "sessionWrite", "sessionDestroy", "sessionGC"); ?>
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.
<?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.
<!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>
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.