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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a dummy database and import 1-db-sess.sql.
  • Change the database settings in 2-lib-sess.php to your own.
  • Access 3-demo.php in the browser and verify the session entry in the database.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

SAVE PHP SESSION INTO DATABASE

All right, let us now get into the mechanics of saving PHP sessions into the database.

 

PART 1) SESSION DATABASE

1-db-sess.sql
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

2-lib-sess.php
<?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) {
    $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, 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.

 

 

PART 3) TESTING

3-demo.php
<?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.

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

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