Tony Marston's Blog About software development, PHP and OOP

Saving PHP's Session data to a database

Posted on 4th July 2005 by Tony Marston

Amended on 11th December 2005

Define database table
Define database class
Define session handler
Amendment History


As you should be aware the HTTP protocol, as used for serving web pages, is completely stateless. This means that after the server has received a request, processed it and sent a response, the process which dealt with that request dies. Anything that the process had in its memory therefore dies with it, so when a subsequent request is received from the same client it is unable to refer to its memory about anything that happened previously.

Fortunately PHP provides a standard method of maintaining memory (state) between requests in the form of Session Handling functions. This allows the programmer to maintain a set of variables in the $_SESSION array which is automatically saved to persistent storage at the end of each script, and then automatically loaded back into memory when a subsequent request is received from a client which supplies the same session_id.

By default the medium used as persistent storage by the session handler will be a series of disk files, one per session, where the file name is the session_id. A file is created when a new session starts, and is deleted when the session terminates (or has expired). This is perfectly adequate for most circumstances, but it has the following drawbacks:

  1. If you are using a shared server then other users of that server may be able to access your session files, thus compromising the security of your site.
  2. Each server will have its own directory where these session files are maintained, so if you are employing load balancing across multiple servers there is no guarantee that a request for an existing session will be given to the server which is maintaining the state for that session.
  3. It would be difficult for a site administrator to perform such queries as "how many sessions are currently active?" or "which users are currently logged in?"

The authors of PHP have provided the ability to store session data using a method other than disk files by means of the session_set_save_handler function. This document will show how I have used this function to store all my session data in my application database.

Define database table

This is how I have defined the database table which will hold all my session data:

CREATE TABLE `php_session` (
  `session_id` varchar(32) NOT NULL default '',
  `user_id` varchar(16) default NULL,
  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_updated` datetime NOT NULL default '0000-00-00 00:00:00',
  `session_data` longtext,
  PRIMARY KEY  (`session_id`),
  KEY `last_updated` (`last_updated`)

Please note the following:

session_id This is the identity of the session, so it must be the primary key.
session_data This must be big enough to hold the largest $_SESSION array that your application is liable to produce.
date_created This is used to identify when the session was started.
last_updated This is used to identify when the last request was processed for the session. This is also used in garbage collection to remove those sessions which have been inactive for a period of time.
user_id This is used to identify the person to whom this session belongs. The value is provided by the application logon screen.

Define database class

Within my development infrastructure it is my practice to use a separate class to access each database table. Each table class is actually a subclass to a generic table class which contains all the functionality which is standard across all database tables. This section identifies the contents of the subclass. A copy of the superclass is contained within the source code for my sample application.

The table class exists in a file called <tablename> which is described in A Data Dictionary for PHP Applications.

require_once '';
class php_Session extends Default_Table
    // ****************************************************************************
    // This class saves the PHP session data in a database table.
    // ****************************************************************************
    // ****************************************************************************
    // class constructor
    // ****************************************************************************
    function __construct ()
        // save directory name of current script
        $this->dirname   = dirname(__file__);
        $this->dbname    = 'audit';
        $this->tablename = 'php_session';
        $this->fieldspec = $this->getFieldSpec_original();
        // there is absolutely NO logging of the audit database
        $this->audit_logging = false;
    } // __construct

The member variable $session_open is for use in the close() method (see below).

The constructor forces the member variable $audit_logging to be false as updates to this database table are not to appear in the audit log.

    // ****************************************************************************
    function open ($save_path, $session_name)
    // open the session.
        // do nothing
        return TRUE;
    } // open

The open() function does not have to do anything as the database is not actually opened until control is passed to my DML class.

    // ****************************************************************************
    function close ()
    // close the session.
        if (!empty($this->fieldarray)) {
            // perform garbage collection
            $result = $this->gc(ini_get('session.gc_maxlifetime'));
            return $result;
        } // if
        return FALSE;
    } // close

The close() function is responsible for calling the gc() function to perform garbage collection. Notice that it obtains its value from the session.gc_maxlifetime parameter in the php.ini file.

    // ****************************************************************************
    function read ($session_id)
    // read any data for this session.
        $fieldarray = $this->_dml_getData("session_id='" .addslashes($session_id) ."'");
        if (isset($fieldarray[0]['session_data'])) {
            $this->fieldarray = $fieldarray[0];
            $this->fieldarray['session_data'] = '';
            return $fieldarray[0]['session_data'];
        } else {
            return '';  // return an empty string
        } // if
    } // read

The read() function is responsible for retrieving the data for the specified session. Note that if there is no data it must return an empty string, not the value NULL.

    // ****************************************************************************
    function write ($session_id, $session_data)
    // write session data to the database.
        if (!empty($this->fieldarray)) {
            if ($this->fieldarray['session_id'] != $session_id) {
                // user is starting a new session with previous data
                $this->fieldarray = array();
            } // if
        } // if
        if (empty($this->fieldarray)) {
            // create new record
            $array['session_id']   = $session_id;
            $array['date_created'] = getTimeStamp();
            $array['last_updated'] = getTimeStamp();
            $array['session_data'] = addslashes($session_data);
        } else {
            // update existing record
            if (isset($_SESSION['logon_user_id'])) {
                $array['user_id']  = $_SESSION['logon_user_id'];
            } // if
            $array['last_updated'] = getTimeStamp();
            $array['session_data'] = addslashes($session_data);
            $this->_dml_updateRecord($array, $this->fieldarray);
        } // if
        return TRUE;
    } // write

The write() function is responsible for creating or updating the database with the session data which is passed to it.

    // ****************************************************************************
    function destroy ($session_id)
    // destroy the specified session.
        $fieldarray['session_id'] = $session_id;
        return TRUE;
    } // destroy

If the session_destroy() function is issued in the code then this will be responsible for deleting the session data from the database.

    // ****************************************************************************
    function gc ($max_lifetime)
    // perform garbage collection.
        $real_now = date('Y-m-d H:i:s');
        $dt1 = strtotime("$real_now -$max_lifetime seconds");
        $dt2 = date('Y-m-d H:i:s', $dt1);
        $count = $this->_dml_deleteSelection("last_updated < '$dt2'");
        return TRUE;
    } // gc

This is the garbage collection or "clean-up" function. The value passed in $max_lifetime is a number of seconds. Any session record which has not been modified within this time limit will be eligible for deletion. Note that this does not mean that the session record will be deleted as soon as the time limit has expired as it depends on when the garbage collection routine is next run. With the method described in this article the garbage collection is only performed when a session closes, which means that a session can never delete its own data. If another session does not terminate then an 'eligible for deletion' session may continue to exist. If this is a problem then the gc() method must be called at the start of the read() method so that any expired data can be deleted before it is read.

    // ****************************************************************************
    function __destruct ()
    // ensure session data is written out before classes are destroyed
    // (see for details)

    } // __destruct
// ****************************************************************************
} // end class
// ****************************************************************************


This destructor method ensures that the session data is written to the database before the DAO (Data Access Object) disappears. See for details

Define session handler

This is as described in the manual for the session_set_save_handler() function. These lines must come before the session_start() function.

require_once 'classes/';
$session_class = new php_Session;
session_set_save_handler(array(&$session_class, 'open'),
                         array(&$session_class, 'close'),
                         array(&$session_class, 'read'),
                         array(&$session_class, 'write'),
                         array(&$session_class, 'destroy'),
                         array(&$session_class, 'gc'));

Notice that the arguments describe methods in a class and not stand-alone functions.


As you should be able to see it is a relatively straightforward process to switch the recording of session data from ordinary disk files to a database table. This overcomes the drawbacks inherent with ordinary disk files:

  1. The session data is more secure as a potential hacker must be able to log into the database before he can access anything.
  2. The use of multiple servers would not create a problem as all session data now resides in a single central place and is accessible by all servers.
  3. It is much easier to query the database should the site administrator require information about current sessions or current users.

Amendment history:

11 December 2005 Updated the comment regarding garbage collection.
29 November 2005 Added a __destruct() method.