PHP: Storing session data in database

Session data by default is stored in /tmp directory on the server in the form of text files. It wouldn’t be a good practice to store session information in the default directory because users can easily get these information. You must either change the directory, or the best practice is to store the information the database. Storing session data in the database not only increase your application security, but also provide you an ability to fetch session related information easily from the database.
PHP provide very easy mechanism and functions for storing session information in the database.
In order to store information in the database you will need to have a table containing fields such sessionId for storing unique session Id, sessionData for storing session data and accessDate that store date when session was last accessed.
Once table is successfully created, saving session requires only two steps.
1. Creating function that interact with and store data in the database.
2. Tell PHP to use the functions defined by using session_set_atuo_save() method.
Now let’s discuss these two steps
1. Creating functions for interacting with database
Before using session_set_auto_save() method, we will need to define six method. These method will then be passed as argument to the session_set_auto_save() method. These six methods are each called on the even taken place. Events are fired when session is started, session is closed, session data is read, session data is written, session data is destroyed, and the six argument/function is used for garbage collection. Garbage collection is used to free resources etc.
Now let’s define and discuss functions for each of these events.
(i) Defining a function which will be called when session is started.

$dbConn = null;

function open_session()
{
    global $dbConn;
    $dbConn = mysqli_connect('host','username','password','dbname') or die('cannot connect to the database');
}

This method is called each time session is started. The function contains code for connecting to the database only. You can place whatever you wish.
(ii) Defining function called when session is closed

function close_session()
{
    global $dbConn;
    $dbConn = mysqli_close();
}

(iii) Defining function called when session is read

function read_session($sid)
{
    global $dbConn;

    $q = sprintf('SELECT data FROM sessions WHERE id="%s"', mysqli_real_escape_string($dbConn, $sid));
    
    $r = mysqli_query($dbConn, $q);
    if (mysqli_num_rows($r) == 1) {
         list($data) = mysqli_fetch_array($r, MYSQLI_NUM);
    return $data;
    } else { 
        return '';
    }
}

This function take current sessionId as argument and read the data from the database.
(iv) Defining function called when session data is written

function write_session($sid, $data)
{
    global $dbConn;

    $q = sprintf('REPLACE INTO sessions (id, data) VALUES ("%s", "%s")',
            mysqli_real_escape_string($dbConn, $sid), mysqli_real_escape_string($dbConn,
                                                                                    $data));
    $r = mysqli_query($dbConn, $q);
    return mysqli_affected_rows($dbConn);
}

This function takes two argument sessions current Id and the data to be written. The code written here inserts session id and data into the database table and return number of affected rows.
(v) Defining function, called when session is destroyed

function destroy_session($sid)
{
    global $dbConn;

    $q = sprintf('DELETE FROM sessions WHERE id="%s"',
                                mysqli_real_escape_string($dbConn, $sid));
    $r = mysqli_query($dbConn, $q);
    $_SESSION = array();
    return mysqli_affected_rows($dbConn);
}

This function take single argument sessionId and delete the row based on this id.
(vi) Defining function that is called when resource need to be freed.

function clean_session($expire) 
{
    global $dbConn;
    
    $q = sprintf('DELETE FROM sessions WHERE DATE_ADD(last_accessed, INTERVAL %d SECOND) < NOW()',
                                                                                    (int)$expire);
    $r = mysqli_query($dbConn, $q);
    return mysqli_affected_rows($dbConn);
}

This function is used to delete session data from the database based on the $expire argument it accepts.

Now as we have defined all the required function, its time to call session_set_auto_save() method.

Before starting the session you would need to call session_set_auto_save() method as

<?php

session_set_auto_save('open_session','close_session','read_session','write_session','destroy_session','clean_session');

session_start();

……..

?>