Save PHP Session In Database (Simple Example)

Welcome to a tutorial on how to save PHP sessions into the database. The default PHP session saves to a temporary file on the server. This works great on a single server but fails miserably on a distributed/cloud setup. But fret not, we can actually “customize” PHP sessions to be saved into the database – Read on for the example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

PART 1) SESSION DATABASE

CREATE TABLE `sessions` (
  `id` varchar(64) NOT NULL,
  `access` bigint(20) UNSIGNED DEFAULT NULL,
  `data` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `sessions`
  ADD PRIMARY KEY (`id`);

This simple database table will replace the temporary session files.

• id The PHP session ID.
• access Last accessed timestamp. Use for garbage collection, to clean up old entries.
• data The session data itself.

 

 

PART 2) CUSTOM SESSIONS CLASS

<?php
// (A) DATABASE SESSION CLASS
class MySess implements SessionHandlerInterface {
  // (A1) PROPERTIES
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  public $lastID = null;
 
  // (A2) INIT - CONNECT TO DATABASE
  public function __construct() {
    $this->pdo = new PDO(
      "mysql:host=".SDB_HOST.";charset=".SDB_CHAR.";dbname=".SDB_NAME,
      SDB_USER, SDB_PASS, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }
 
  // (A3) HELPER - EXECUTE SQL QUERY
  function exec ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
    $this->lastID = $this->pdo->lastInsertId();
  }
 
  // (A4) SESSION OPEN - NOTHING IN PARTICULAR...
  function open ($path, $name) { return true; }
 
  // (A5) SESSION CLOSE - CLOSE DATABASE CONNECTION
  function close () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
    return true;
  }
 
  // (A6) SESSION READ - FETCH FROM DATABASE
  function read ($id) {
    $this->exec("SELECT `data` FROM `sessions` WHERE `id`=?", [$id]);
    $data = $this->stmt->fetchColumn();
    return $data===false ? "" : $data ;
  }
 
  // (A7) SESSION WRITE - WRITE INTO DATABASE
  function write ($id, $data) {
    $this->exec("REPLACE INTO `sessions` VALUES (?, ?, ?)", [$id, time(), $data]);
    return true;
  }
 
  // (A8) SESSION DESTROY - DELETE FROM DATABASE
  function destroy ($id) {
    $this->exec("DELETE FROM `sessions` WHERE `id`=?", [$id]);
    return true;
  }
 
  // (A9) GARBAGE COLLECT - DELETE OLD ENTRIES
  function gc ($max) {
    $this->exec("DELETE FROM `sessions` WHERE `access` < ?", [(time() - $max)]);
    return true;
  }
}
 
// (B) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("SDB_HOST", "localhost");
define("SDB_CHAR", "utf8mb4");
define("SDB_NAME", "test");
define("SDB_USER", "root");
define("SDB_PASS", "");
 
// (C) START!
session_set_save_handler(new MySess(), true);
session_start();

Next, we have a library to process the database session.

• (A) class MySess implements SessionHandlerInterface This is a “special class” to do our own database sessions.
• (A1, A2, A5) When new MySess() is created, it will automatically connect to the database. When the session is closed, it disconnects.
• (A3) exec() A helper function to run SQL queries.
• (A4 To A9) We need to define 6 functions to handle the session accordingly.
  • open() When the session starts. In file-based sessions, this is where we create/open the temporary session file. But since we are using the database now, there’s nothing to do here in particular…
  • close() When the session closes, we close the database connection as well.
  • read() Fetch the session data from the database.
  • write() Save the session data into the database.
  • destroy() When the session is destroyed, delete the session data from the database.
  • gc() Garbage collection, delete all old entries.
• (B) Self-explanatory. Change the settings to your own.
• (C) Tell PHP to use the custom library to handle sessions, and start the session.

P.S. For A4 to A9, all the session functions must return true.

 

 

PART 3) TESTING

<?php
require "2-lib-sess.php";
$_SESSION["test"] = time();
print_r($_SESSION);

With that, the final step is to use the library, and verify that the database session is working. The end.

 

 

 

LINKS & REFERENCES

• PHP Session Handler Interface – PHP
• PHP Cross Domain Session – Code Boxx
• PHP Sessions (Very Simple Example) – Code Boxx