PHP Store Session In MySQL Database

This article describes How to store PHP session in MySQL database instead files on the web server. The method should be considered when your web application runs on multiple web server or in a clustered environment. Especially with e-commerce websites with high amount of transactions or forums/portals that have huge member/customer database with million hits per day.

Or just because sessions aren’t operated properly on your shared hosting (you may know that shared hosting environment is always crazy and unpredictable).

Store Session In MySQL Database Using PHP

The following displays steps to configure session stored in database.

1. Create a table to store the session information

Before starting storing session in MySQL database, we need to have a new table, right?

 
--
-- Table structure for table `4rd_sessions`
--
 
CREATE TABLE IF NOT EXISTS `4rd_sessions` (
  `id` VARCHAR(32) NOT NULL,
  `access` INT(10) UNSIGNED DEFAULT NULL,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Let’s execute the SQL script above to create a new table in your current web application MySQL database or separated database to store session.

Session Table In MySQL Database

2. Set user-level session storage functions

Then we need to override the default session mechanism by using PHP session_set_save_handler ( callback $open , callback $close , callback $read , callback $write , callback $destroy , callback $gc ) function.

session_set_save_handler() sets the user-level session storage functions which are used for storing and retrieving data associated with a session. This is most useful when a storage method other than those supplied by PHP sessions is preferred. i.e. Storing the session data in a local database.

Below is the implementation of those functions:

<?php
session_set_save_handler('_open',
                         '_close',
                         '_read',
                         '_write',
                         '_destroy',
                         '_clean');
 
function _open()
{
    global $_sess_db;
 
    $db_user = "root";
    $db_pass = "";
    $db_host = 'localhost';
 
	$db_name = '_demo';
 
    if ($_sess_db = mysql_connect($db_host, $db_user, $db_pass))
    {
        return mysql_select_db($db_name, $_sess_db);
    }
 
    return FALSE;
}
 
function _close()
{
    global $_sess_db;
 
    return mysql_close($_sess_db);
}
 
function _read($id)
{
    global $_sess_db;
 
    $id = mysql_real_escape_string($id);
 
    $sql = "SELECT data
            FROM   4rd_sessions
            WHERE  id = '$id'";
 
    if ($result = mysql_query($sql, $_sess_db))
    {
        if (mysql_num_rows($result))
        {
            $record = mysql_fetch_assoc($result);
 
            return $record['data'];
        }
    }
 
    return '';
}
 
function _write($id, $data)
{   
    global $_sess_db;
 
    $access = time();
 
    $id = mysql_real_escape_string($id);
    $access = mysql_real_escape_string($access);
    $data = mysql_real_escape_string($data);
 
    $sql = "REPLACE 
            INTO    4rd_sessions
            VALUES  ('$id', '$access', '$data')";
 
    return mysql_query($sql, $_sess_db);
}
 
function _destroy($id)
{
    global $_sess_db;
 
    $id = mysql_real_escape_string($id);
 
    $sql = "DELETE
            FROM   4rd_sessions
            WHERE  id = '$id'";
 
    return mysql_query($sql, $_sess_db);
}
 
function _clean($max)
{
    global $_sess_db;
 
    $old = time() - $max;
    $old = mysql_real_escape_string($old);
 
    $sql = "DELETE
            FROM   4rd_sessions
            WHERE  access < '$old'";
 
    return mysql_query($sql, $_sess_db);
}
?>

Explanation:

• 1. _open(): open a connection to the database that contains session table. You need to change value of database setting variables with yours.
• 2. _close(): simply close the database connection.
• 3. _read(): read/get value of a particular session id.
• 4. _write(): assign/set value for a particular session id.
• 5. _destroy(): delete the session from the database. Meaning session information no longer exists.
• 6. _clean(): delete all expired sessions. This function should be called in a cronjob or task scheduler to clean the session table.

Let’s save as a PHP file, ex: manage_session.php; it will be included in all pages that need to write/read session.

3. Write/Assign Session Value

<?php
	include("manage_session.php");
	session_start();
 
	$_SESSION["sesion1"] = "4rapiddev";
	$_SESSION["sesion2"] = "4 Rapid Development";	
?>

Note: session_start() must be called after the session_set_save_handler implementation.

4. Read/Get Session Value

<?php
	include("manage_session.php");
	session_start();
 
	echo "sesion1: " . $_SESSION["sesion1"] . "<br>";	//4rapiddev
	echo "sesion2: " . $_SESSION["sesion2"] . "<br>";	//4 Rapid Development
?>

Note: you should use Mysql Profiler to monitor all SQL statement sent to your MySQL Database to make sure it works correctly.

[download id=”14″ format=”1″]