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
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
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
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
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.
EXTRAS
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
- PHP Session Handler Interface – PHP
- PHP Cross Domain Session – Code Boxx
- PHP Sessions (Very Simple Example) – Code Boxx
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!
Thanks for this useful tutorial.
Just a question (I’m new to SessionHandlerInterface): what is $path in open function?
session_set_save_handler() – Will create new MySess, constructor gets called.
session_start() – Open gets called. $path is the temp PHP folder, and $name is the session file name. We are not creating a session file now, thus nothing to do here.