Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using MySQL to store PHP session variables

Sessions

Using MySQL to store PHP session variables

by sleipnir214 Posted Jun 14, 2002 (Edited Apr 22, 2004)

[STANDARD DISCLAIMER: There is PHP script code in this FAQ. The code herein works for me, but it might blow up your application. Use with caution, prudence, circumspection and expectation of great calamity. Troubleshoot well.]

PHP, in its default configuration, stores session variables in small files on the file system. I wrote an application that is served by a group of web servers, so I needed each server in the farm to be able to recall the session variables set by any other server. Storing session variables in MySQL solved the problem.

This FAQ is based on text and code found in a tutorial titled ["]Custom Session Handlers in PHP4["], by Ying Zhang (http://www.phpbuilder.com/columns/ying20000602.php3). The code in the original tutorial, however, has some lines of code which are in conflict with the PHP online manual and has been shown to cause segmentation faults in some versions of PHP.

There are four parts to storing session information on a SQL database server. This FAQ will use MySQL as its example, but the code can be easily converted to use another database server.

1. First, edit php.ini. There is a line in that file that by default reads:

[tt]session.save_handler = files[/tt]

Change that line to read:

[tt]session.save_handler = user[/tt]

Restart PHP (most likely by restarting your web server) to make the changes take effect.

2. Create your database in MySQL, and create a table in which to store your session information. The example code below uses a table called ["]session["]. Here is the SQL command which created it:

[tt]CREATE TABLE sessions (
sesskey varchar(32) NOT NULL default ',
expiry int(10) unsigned NOT NULL default '0',
value text NOT NULL,
PRIMARY KEY (sesskey)
);[/tt]

Create the user that your code will use to access the database, and GRANT it INSERT, UPDATE, and DELETE privileges.

3. Create the functions which will open, close, read, write, destroy, and garbage collect session variables.

4. Register those functions as session handlers in PHP using the session_set_save_handler() function.

Here is the code I use. I found this code on a tutorial web site and modified it for my purposes. I have put this code in a file which I include at the beginning of each script file where I need to use session variables. Any session variables you wish to keep can be installed in the session storage either through the session_register() function, by adding a key to the $_SESSION superglobal variable (PHP version >= 4.1.0) or by adding a key to the $HTTP_SESSION_VARS global variable.

If your session variables are objects, you will need to put your class definitions in here, too. That way PHP will know how to interpret your serialized session data.

Code:

<?php
$SESS_DBHOST  = "localhost";        /* stores database server hostname */
$SESS_DBNAME  = "test";      /* store database name */
$SESS_DBTABLE = "test";         /* stores table name */
$SESS_DBUSER  = "test";              /* stores database user */
$SESS_DBPASS  = "test";              /* stores database password */

$SESS_LIFE = ini_get("session.gc_maxlifetime");



/* This is the function that will be registered as the "open" function of our
   sessions.  It creates a persistent database connection to the database if one
   does not exist.  If the connection has already been opened, PHP will return
   handle of the previously opened handle.  See the PHP online manual listing
   for mysql_pconnect for more information.
*/

function sess_open($save_path, $session_name)
{
     global $SESS_DBHOST, $SESS_DBNAME, $SESS_DBUSER, $SESS_DBPASS, $SESS_DBH;

     $SESS_DBH = mysql_pconnect($SESS_DBHOST, $SESS_DBUSER, $SESS_DBPASS);

     if ($SESS_DBH == FALSE)
     {
          print "Can't connect to $SESS_DBHOST as $SESS_DBUSER";
          print "MySQL Error: ";

          print mysql_error();
          die;
     }

     if (! mysql_select_db($SESS_DBNAME, $SESS_DBH))
     {
          print "Unable to select database $SESS_DBNAME";
          die;
     }
     return true;
}

 

/* This function will be registered to close session handling.  Normally,
   this means finishing all writes and closing the session store on the
   filesystem.  This function does nothing.  We are using persistent database
   connections so we never close the connection.
*/
                                     
function sess_close()
{
     return true;
}



/* This function will retrieve session variables for a particular session key.  Each
   time a session is read from the database, this function "freshens" the session
   so that it will not be removed by garbage collection.  PHP handles de-serializing
   the data for you.
*/
function sess_read($key)
{
     global $SESS_DBH, $SESS_LIFE, $SESS_DBTABLE;

     $qry = "SELECT value FROM $SESS_DBTABLE WHERE sesskey = '$key' AND expiry > " . time();
     $qid = mysql_query($qry, $SESS_DBH);

     if (list($value) = mysql_fetch_row($qid))
     {
          $expiry = time() + $SESS_LIFE;
          $qry = "UPDATE $SESS_DBTABLE SET expiry = $expiry WHERE sesskey = '$key'";
          $qid = mysql_query($qry, $SESS_DBH);
          return $value;
     }

     return "";
}



/* This function stores the session data for a particular key.  If the key
   already exists in the table, it updates the key's value and expiry time.
   PHP serializes the session data and sends it in as a string.
*/
function sess_write($key, $val)
{
     global $SESS_DBH, $SESS_LIFE, $SESS_DBTABLE;

     $expiry = time() + $SESS_LIFE;
     $value = addslashes($val);

     $qry = "INSERT INTO $SESS_DBTABLE VALUES ('$key', $expiry, '$value')";
     $qid = mysql_query($qry, $SESS_DBH);
     if (! $qid)
     {
          $qry = "UPDATE $SESS_DBTABLE SET expiry = $expiry, value = '$value' WHERE sesskey = '$key' AND expiry > " . time();
          $qid = mysql_query($qry, $SESS_DBH);
     }

     return $qid;
}



/* This destroys a key by deleting its record from the table
*/
function sess_destroy($key)
{
     global $SESS_DBH;

     $qry = "DELETE FROM $SESS_DBTABLE WHERE sesskey = '$key'";
     $qid = mysql_query($qry, $SESS_DBH);

     return $qid;
}



/* This is the function which collects garbage.  The probability that PHP
   will run this function is set in the php.ini directive
   "session.gc_probability"
*/
function sess_gc ($maxlifetime)
{
     global $SESS_DBH, $SESS_LIFE;

     $qry = "DELETE FROM $SESS_DBTABLE WHERE expiry < " . time();
     $qid = mysql_query($qry, $SESS_DBH);

     return mysql_affected_rows($SESS_DBH);
}



/* This registers the above functions as the handlers for the various operations
   on session data.
*/
session_set_save_handler
     (
          "sess_open",
          "sess_close",
          "sess_read",
          "sess_write",
          "sess_destroy",
          "sess_gc"
     );


session_cache_limiter('public');
session_start();

?>

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Accept Learn more…

Back

Top