Hi.
I am utilising PHP sessions with a database rather than the traditional file storage. I currently am running into an issue with session regeneration. For the sign in, we regenerate the session id everytime and as expected the session handler destroys the session, makes a new session id and the the user_id column data will be missing but then i run a new update query to insert the user_id back in but it won’t work at all. I think i am being very thick as it’s been a long day but please can someone help.
I also think i may be going about this implementation wrong as regeneration needs to happen automatically every so often. Effectively, we require the sessions to be stored in a database so they can be remotely revoked by the user from any device they are signed into. But this session table is for any sessions, so the user_id can be null to support guest experiences. Any help would be greatly appreciated.
Please see laravel: HTTP Session - Laravel - The PHP Framework For Web Artisans
I also would utilise a framework but this is for a project where that isn’t possible… also tbh it’s shown i need to polish up on my vanilla PHP skills. Thanks 
The table has 4 columns:
session_id
session_data
session_lastaccesstime
user_id (optional)
My SessionHandler class looks like this:
<?php
class CustomSessionHandler implements \SessionHandlerInterface
{
private $pdo;
public function __construct($database)
{
$this->pdo = $database;
}
public function open($savePath, $sessionName): bool
{
print "Session opened.\n";
return true;
}
public function close(): bool
{
print "Session closed.\n";
return true;
}
public function read($sessionId): string|false
{
$sql = $this->pdo->prepare("SELECT * FROM sessions WHERE session_id = :id");
$sql->execute(['id' => $sessionId]);
$data = $sql->fetch();
if ($data) {
$sql = "UPDATE sessions SET session_lastaccesstime = NOW() WHERE session_id = :id";
$this->pdo->prepare($sql)->execute(['id' => $sessionId]);
return $data['session_data'];
} else {
return '';
}
}
public function write($sessionId, $data): bool
{
print "Session value written.\n";
print "Sess_ID: $sessionId\n";
$sql = $this->pdo->prepare("INSERT INTO sessions SET session_id = :id, session_data = :session_data, session_lastaccesstime = NOW() ON DUPLICATE KEY UPDATE session_data = :session_data");
$sql->execute(['id' => $sessionId, 'session_data' => $data]);
return true;
}
public function destroy($sessionId): bool
{
print "Session destroyed.\n";
print "Sess_ID: $sessionId\n";
$sql = $this->pdo->prepare("DELETE FROM sessions WHERE session_id=:id");
$sql->execute(['id' => $sessionId]);
return true;
}
public function gc($lifetime): int
{
$this->pdo->query("DELETE FROM sessions WHERE session_lastaccesstime < DATE_SUB(NOW(), INTERVAL " . $lifetime . " SECOND)");
return true;
}
}
I then initialise with this:
$dsn = 'mysql:host=localhost;dbname=artefact;charset=utf8';
$username = 'root';
$password = '';
try {
$db = new PDO($dsn, $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
echo 'connected';
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
$handler = new CustomSessionHandler($db);
session_set_save_handler($handler, true);
register_shutdown_function('session_write_close');
session_start();
$_SESSION['chicken'] = "kfc";
Dummy sign in code:
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
if ($_POST['action'] == 'login')
{
// pretend submitted credentials, all good
// db returns
$user = [
'id' => 1,
'username' => "JohnDoe123",
];
$_SESSION['user_id'] = $user['id'];
$_SESSION['username'] = $user['username'];
//session_regenerate_id(true); -- uncomment this and then the update query will not be executed
$sql = "UPDATE sessions SET user_id = :user_id WHERE session_id = :id";
$db->prepare($sql)->execute(['user_id' => $user['id'], 'id' => session_id()]);
}
if ($_POST['action'] == 'logout') {
$_SESSION = array();
$cookie_par = session_get_cookie_params();
setcookie(session_name(), '', time() - 86400, $cookie_par['path'], $cookie_par['domain'], $cookie_par['secure'], $cookie_par['httponly']);
session_destroy();
}
}
<?php if (isset($_SESSION['user_id'])) { ?>
logged in
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input type="hidden" name="action" value="logout" />
<button type="submit">fake sign out</button>
</form>
<?php } else { ?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input type="hidden" name="action" value="login" />
<button type="submit">fake sign in</button>
</form>
<?php } ?>
Thanks in advance