PHP Database Sessions

This tutorial will teach the reader how to create database sessions for their PHP and MySQL website. By the end of the tutorial the user should be able to confidently add PHP database sessions to their website and understand how using these type of sessions is better than normal text file based sessions.

Tutorial Note:

This tutorial was completed in Ubuntu 9.04 and so reflects the processes required to complete this task within Linux. If some one requests a Windows version I will be happy to provide one.


To begin this (as a noob to PHP) I searched around on Google. I read up one site in particular [Dev Shed – Storing PHP Sessions in a Database]. Dev Shed provides and very neat tutorial on database sessions. I will quickly provide a 5 point overview of the immediate up shots you will like and notice:

  • Security – It is harder to hack the web server and then the database than it is to just hack the web server
  • Speed – It is surprisingly fast, almost as fast as normal sessions. In fact I am not sure if you will tell the difference
  • Ease of learning – Database session handling is very easy to learn making it very easy to pick up and manipulate to your own advantage
  • Data Storage – The standardised data storage within the table allows the custom management and data manipulation of the session information allowing for the administrator to choose when sessions expire. This point also allows for you to judge which users are on line etc.
  • Ease of use – You don’t have to make up some special code to use it. Just include the handler class and use PHP sessions as standard.

The Dev Shed site will cover all the intricates of the positive sides to using PHP database sessions. What I am going to do now I move straight onto the coding, trying to make this tutorial as simple and concise to read as possible.

As a further note before I begin I use a strange database layout for some noobs. This database format is called PEAR:DB. This has been explained within other areas of my blog if you wish to read up on that, there are also up shots to using PEAR:DB as well as down sides. The first step, as always, is to produce the database table:

  `session_id` varchar(250) NOT NULL,
  `UserID` int(11) default NULL,
  `session_data` longtext NOT NULL,
  `expires` varchar(250) NOT NULL,
  PRIMARY KEY  (`session_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='session database storage';

This is not the best formatted table but it will give you a good idea of what is needed. This is formatted as SQL create table statement (created from phpMyAdmin) so it can be posted straight into your database client for usage. Now most of the fields are self explanatory but I will go through each one:

  • session_id – This houses the session id of the current session. This is the PHP session id not your own.
  • UserID – This is a field created by yours truly. This allows me to store the user identification number and use it later to understand which user that session belongs to (detect on line users).
  • session_data – This field just contains all the session data for that session (all the random stuff you put into $_SESSION)
  • expires – This field is compared for garbage collection to understand whether a session has expired or not and if it has the script will delete the session detailing that the user is off-line.

Now that the table for database sessions has been explained below you will see the main file that houses the database session management. After explaining this I will show you how to implement these sessions into your site. Remember this class is a handler, it is not the session itself it just reads and saves the session and deletes it if necessary.


// Filename: Session.php

//Adds my database. I have explained earlier in the blog how naming your connection dbconnection will get you hacked.

//Handler class begins
class SessionManager {

   var $life_time;
   var $db = null;
   //class function. This houses the actual save handler (session_set_save_handler) which then houses an array telling the sessions how to save (what methods to use)
   function SessionManager(&amp;amp;amp;amp;amp;amp;amp;amp;amp;$db) {

      // Read the maxlifetime setting from PHP
      $this->life_time = get_cfg_var("session.gc_maxlifetime");

      // Register this object as the session handler
        array( &amp;amp;amp;amp;amp;amp;amp;amp;amp;$this, "open" ), 
        array( &amp;amp;amp;amp;amp;amp;amp;amp;amp;$this, "close" ),
        array( &amp;amp;amp;amp;amp;amp;amp;amp;amp;$this, "read" ),
        array( &amp;amp;amp;amp;amp;amp;amp;amp;amp;$this, "write"),
        array( &amp;amp;amp;amp;amp;amp;amp;amp;amp;$this, "destroy"),
        array( &amp;amp;amp;amp;amp;amp;amp;amp;amp;$this, "gc" )
      //read in the db object my api uses
      $this->db = $db;

	//this opens the session for reading or writing
   function open( $save_path, $session_name ) {

      global $sess_save_path;

      $sess_save_path = $save_path;

      // Don't need to do anything. Just return TRUE.
      return true;


   function close() {

      return true;


   //this reads all you session data from the table into $_SESSION whilst checking the session has not expires. Also makes a new session if needed.
   function read( $id ) {

      // Set empty result
      $data = '';

      // Fetch session data from the selected database
      $time = time();

      $newid = $this->db->quote($id);
      $sql = "SELECT session_data FROM sessions WHERE
			session_id = $newid AND expires > $time";

      //add PEAR here
		$result = $this->db->getRow($sql);
		if ( is_object($result) ) {
        	$data = $result->session_data;

      return $data;


   //writes all your $_SESSION data to database
   function write( $id, $data ) {

      // Build query                
      $time = time() + $this->life_time;

      $newid = $this->db->quote($id);
      $newdata = $this->db->quote($data);

      $sql = "REPLACE sessions
		(session_id, session_data, expires) VALUES($newid, $newdata, $time)";
	  		$userIden = $this->db->quote($_SESSION['uid']);
	  		$sql = "REPLACE sessions
		(session_id, UserID, session_data,expires) VALUES($newid, $userIden, $newdata, $time)";

      return TRUE;


   //Now this is complex because normally you can just use session_destroy or whatever but due to a bug in PHP which is meant to be fixed you had to logout the user manually. Best to look at my user class for help on this.
   function destroy( $id ) {

      // Build query
      $newid = $this->db->quote($id);
      $sql = "DELETE FROM `sessions` WHERE `session_id` = '$newid'";

      //and again

      return TRUE;


   //This will delete any old sessions which should no longer exist. For this to work properly you must set carbage collection within your php.ini to 100% of the time.
   //this is done within the session.gc_probability value in your php.ini, set it to 100 for 100%
   function gc() {
      // Garbage Collection

      // Build DELETE query.  Delete all records who have passed the expiration time
      $sql = 'DELETE FROM sessions WHERE expires < UNIX_TIMESTAMP();';
      //and finally here

      // Always return TRUE
      return true;




Now if you read the code above you will see I explained not only the file name but also the processes for the session handler. I should explain my concerned comment at “function destroy($id)”. The reason I said you cannot use conventional session_destroy to logout a user when using database sessions is because of a bug that existed within one of the PHP versions (and I have never decided to check if this bug has been fixed since my manual version works perfect). This bug would mean that when using database sessions if you were to log the user out with “session_destroy();” the session could not be recreated…meaning that the user would have to close their browser before they could login again (or just redirect away and back again). I am not sure which version this bug has been fixed on so I just stick with my manual logout.

The session save handler has been explained along with its table. It is time to finish this tutorial with the usage and an example. I find a lot of tutorials are missing detailed explanations about these sort of things and expect you to almost know from thin air how to use this stuff (luckily I am not one of those people).


        //include the session.php database session handler from above

        //Set the ini values so that the script works properly
        ini_set('session.gc_probability', 100);
        ini_set('session.gc_divisor', 100);

        //me passing my database object to it. This can also be done (and should be) with normal MySQL
	$sess = new SessionManager($db);

        //start session as you would normally

        //Add and ech as you would normally. The session.php is only a handler for the storage of data it does not over ride the default session.
        $_SESSION['username'] = "hello, I am the User. This session is using database sessions.";

        echo $_SESSION['username'];

And there you have it database sessions and as stated in the example script above there is no need to change your session type! Enjoy 🙂


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s