PHP : Web Basics - [8.7] Storing Sessions in a Database

8.7.1 Problem

You want to store session data in a database instead of in files. If multiple web servers all have access to the same database, the session data is then mirrored across all the web servers.

8.7.2 Solution

Set session.save_handler to user in php.ini and use the pc_DB_Session class shown in Example 8-1. For example:

$s = new pc_DB_Session('mysql://user:password@localhost/db');
ini_get('session.auto_start') or session_start();

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;
    }