Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Using MySQL to store PHP session variables by sleipnir214
Posted: 15 Jun 02 (Edited 22 Apr 04)

[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:

          session.save_handler = files

     Change that line to read:

          session.save_handler = user

     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:

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

     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.


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

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

     if (! mysql_select_db($SESS_DBNAME, $SESS_DBH))
          print "Unable to select database $SESS_DBNAME";
     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)

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

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



Back to PHP FAQ Index
Back to PHP Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close