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.
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″]