Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

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 parameterthe value of
session.save_pathis used to select a
database after a connection to a MySQL server is established. The
$table_name parameterthe value of
session.nameis 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.