Storing Sessions in a Database (PHP Cookbook)

8.7.3. Discussion

One of the most powerful aspects of the session module is its abstraction of how sessions get saved. The session_set_save_handler( ) function tells PHP to use different functions for the various session operations such as saving a session and reading session data. The pc_DB_Session class stores the session data in a database. If this database is shared between multiple web servers, users' session information is portable across all those web servers. So, if you have a bunch of web servers behind a load balancer, you don't need any fancy tricks to ensure that a user's session data is accurate no matter which web server they get sent to.

To use pc_DB_Session, pass a data source name (DSN) to the class when you instantiate it. The session data is stored in a table called php_session whose structure is:

CREATE TABLE php_session (
  id CHAR(32) NOT NULL,
  data MEDIUMBLOB,
  last_access INT UNSIGNED NOT NULL,
  PRIMARY KEY(id)
)

If you want the table name to be different than php_session, set session.save_path in php.ini to your new table name. Example 8-1 shows the pc_DB_Session class.

Example 8-1. pc_DB_Session class

require 'PEAR.php';
require 'DB.php';

class pc_DB_Session extends PEAR {

    var $_dbh;
    var $_table;
    var $_connected = false;
    var $_gc_maxlifetime;
    var $_prh_read;
    var $error = null;

    /**
     * Constructor
     */
    function pc_DB_Session($dsn = null) {
        if (is_null($dsn)) { 
            $this->error = PEAR::raiseError('No DSN specified');
            return;
        }

        $this->_gc_maxlifetime = ini_get('session.gc_maxlifetime');
        // Sessions last for a day unless otherwise specified. 
        if (! $this->_gc_maxlifetime) {
            $this->_gc_maxlifetime = 86400;
        }

        $this->_table = ini_get('session.save_path');
        if ((! $this->_table) || ('/tmp' == $this->_table)) {
            $this->_table = 'php_session';
        }

        $this->_dbh = DB::connect($dsn);
        if (DB::isError($this->_dbh)) {
            $this->error = $this->_dbh;
            return;
        }

        $this->_prh_read = $this->_dbh->prepare(
            "SELECT data FROM $this->_table WHERE id LIKE ? AND last_access >= ?");
        if (DB::isError($this->_prh_read)) {
            $this->error = $this->_prh_read;
            return;
        }

        if (! session_set_save_handler(array(&$this,'_open'),
                                       array(&$this,'_close'),
                                       array(&$this,'_read'),
                                       array(&$this,'_write'),
                                       array(&$this,'_destroy'),
                                       array(&$this,'_gc'))) {
            $this->error = PEAR::raiseError('session_set_save_handler() failed');
            return;
        }

        return $this->_connected = true;
    }

    function _open() {
        return $this->_connected;
    }
    
    function _close() {
        return $this->_connected;
    }

    function _read($id) {
        if (! $this->_connected) { return false; }
        $sth = 
            $this->_dbh->execute($this->_prh_read,
                                 array($id,time() - $this->_gc_maxlifetime));
        if (DB::isError($sth)) {
            $this->error = $sth;
            return '';
        } else {
            if (($sth->numRows() == 1) && 
                ($ar = $sth->fetchRow(DB_FETCHMODE_ORDERED))) {
                return $ar[0];
            } else {
                return '';
            }
        }
    }

    function _write($id,$data) {
        $sth = $this->_dbh->query(
            "REPLACE INTO $this->_table (id,data,last_access) VALUES (?,?,?)", 
            array($id,$data,time()));
        if (DB::isError($sth)) {
            $this->error = $sth;
            return false;
        } else {
            return true;
        }
    }

    function _destroy($id) {
        $sth = $this->_dbh->query("DELETE FROM $this->_table WHERE id LIKE ?",
                                  array($id));
        if (DB::isError($sth)) {
            $this->error = $sth;
            return false;
        } else {
            return true;
        }
    }

    function _gc($maxlifetime) {
        $sth = $this->_dbh->query("DELETE FROM $this->_table WHERE last_access < ?", 
                                  array(time() - $maxlifetime));
        if (DB::isError($sth)) {
            $this->error = $sth;
            return false;
        } else {
            return true;
        }
    }
}

The pc_DB_Session::_write( ) method uses a MySQL-specific SQL command, REPLACE INTO, which updates an existing record or inserts a new one, depending on whether there is already a record in the database with the given id field. If you use a different database, modify the _write( ) function to accomplish the same task. For instance, delete the existing row (if any), and insert a new one, all inside a transaction:

    function _write($id,$data) {
        $sth = $this->_dbh->query('BEGIN WORK');
        if (DB::isError($sth)) {
            $this->error = $sth;
            return false;
        }        
        $sth = $this->_dbh->query("DELETE FROM $this->_table WHERE id LIKE ?",
                                  array($id));
        if (DB::isError($sth)) {
            $this->error = $sth;
            $this->_dbh->query('ROLLBACK');
            return false;
        }        
        $sth = $this->_dbh->query(
            "INSERT INTO $this->_table (id,data,last_access) VALUES (?,?,?)", 
            array($id,$data,time()));
        if (DB::isError($sth)) {
            $this->error = $sth;
            $this->_dbh->query('ROLLBACK');
            return false;
        }
        $sth = $this->_dbh->query('COMMIT');
        if (DB::isError($sth)) {
            $this->error = $sth;
            $this->_dbh->query('ROLLBACK');
            return false;
        }
             return true;
    }

Example 8-1. pc_DB_Session class

8.7. Storing Sessions in a Database

8.7.1. Problem

8.7.2. Solution

8.7.3. Discussion

8.7.4. See Also