Database PHP Session Handling

Database PHP Session Handling

As you should be aware the HTTP protocol, as used for serving web pages, is completely stateless. This means that after the server has received a request, processed it and sent a response, the process which dealt with that request dies. Anything that the process had in its memory therefore dies with it, so when a subsequent request is received from the same client it is unable to refer to its memory about anything that happened previously.

Database PHP Session Handling – Details

PHP provides a standard method of maintaining memory (state) between requests in the form of Session Handling functions. This allows the programmer to maintain a set of variables in the $_SESSION array which is automatically saved to persistent storage at the end of each script, and then automatically loaded back into memory when a subsequent request is received from a client which supplies the same session_id.

This article will explain how to create a Session Handler that stores the session information in a MySQL database. This is useful if you want more control over managing sessions and especially useful in terms of scalability. If you need to expand into a cloud hosting environment or have multiple geographically based servers setup, it is much easier to provide transparent session handling across these servers if you are utilizing database session handling.

The first step is to create a Session table in your MySQL database. Here is the CREATE query to create this table:

CREATE TABLE `sessions` (
  `id` varchar(32) NOT NULL,
  `access` int(10) unsigned default NULL,
  `data` text,
  PRIMARY KEY  (`id`)

As you can see it is a very simple design. The id column will store the Session ID generated by PHP, the access column will contain a timestamp of the last access time of the session (used for timing sessions out and removing expired sessions), and the data column is used to store the serialized session data which will be restored in the $_SESSION super global in PHP.

The next step is to create the PHP Session handling class and apply the required class methods to the PHP session handler routines. Here is a very simple PHP class:

	class sessionHandler {
		private $_session ;
		public $maxTime ;
		private $db ;
		private $timeout_limit = 108000 ;
                // Constructor: sets up the database connection and binds the PHP session handler functions to the class functions
		public function __construct( ) {
			$this->db = Database::obtain( ) ;
			$this->maxTime['access'] = time( ) ;
			$this->maxTime['gc'] = $this->timeout_limit ;
			session_set_save_handler(array($this, '_open'),
									array($this, '_close'),
									array($this, '_read'),
									array($this, '_write'),
									array($this, '_destroy'),
									array($this, '_clean')
			register_shutdown_function( 'session_write_close' ) ;
			@session_start( ) ; // Start the session
		public function _open( ) {
			return true ;	
		public function _close( ) {
			$this->_clean( $this->maxTime['gc'] ) ;
			return true ;	
		public function _read( $id ) {
			$sql = "SELECT data FROM sessions AS Session WHERE'$id'" ;
			$allData = $this->db->fetch_array( $sql ) ;

			$totalData = count( $allData ) ;
			$hasData = (bool) $totalData >= 1 ;
			return $hasData ? $allData[0]['data'] : '' ;
		public function _write( $id, $data ) {
			$expiry = time( ) + $this->maxTime['gc'] ;
			$sql = "REPLACE INTO sessions VALUES ('$id', ".$expiry.", '$data')" ;
			return $this->db->query( $sql ) ;			
		public function _destroy( $id ) {
			$sql = "DELETE FROM sessions WHERE id='$id'" ;
			return $this->db->query( $sql ) ;
		public function _clean( $max ) {
			$sql = "DELETE FROM sessions WHERE access < ".time() ;
			return $this->db->query( $sql ) ;

You will also notice that I am not using your typical PHP MySQL database functions. I am actually using a Database Singleton Class. You can learn more about this from this post.

The last thing you need to do is include this session class at the top of every page request. Something like:

include( "libs/sessionHandler.class.php" ) ;
$session = new sessionHandler( ) ;

You can download my Sample Source code Here.


About mfrank

Professional Developer of SwhistleSoft
This entry was posted in MYSQL, php development, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *