Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home PHP PHP Store Session In MySQL Database

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;

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

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

<?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";	
?>

<?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
?>

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

Feb 28, 2012Hoan Huynh
ASP.NET Store Session In MS SQL Server DatabaseCreate Count Down Download Page With JavaScript
You Might Also Like:
  • ASP.NET Store Session In MS SQL Server Database
  • Mysql restore database from dump file with GZIP compression
  • Schedule Backup And Zip MySQL Database In Windows
  • PHP Load Facebook Albums And Save To MySQL Database
  • MySql backup database with gzip compression
  • MySQL dump table
  • PHP Connect To MySQL Read Data And Export XML Format
  • Change or reset MySQL Root Account Password
  • Where Are ASPStateTempApplications and ASPStateTempSessions Tables?
  • Install LAMP (Apache, MySQL and PHP in Linux) on CentOS
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

9 years ago PHPmysql_connect, mysql_real_escape_string, mysql_select_db, session_set_save_handler, session_start429
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
22,563 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
20,340 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
16,078 views
JQuery Allow only numeric characters or only alphabet characters in textbox
13,572 views
C# Read Json From URL And Parse/Deserialize Json
10,107 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Essay Writing Services
  • Learn Photo-editing – Learn to Enhance Photos With Photoediting Software
  • The Best Way to Loan Calculator Can Help You Save Money
  • Custom Research Paper – What Exactly Can It Entail?

  • Essay Writing Service – Take Care of Your Work!

Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (360)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development