I'm trying to get session data to save to our MySQL database and, of course, be accessible on other pages. I'd really like to get the session_set_save_handler to work properly rather than working with memcache or some of the other solutions I've seen posted.
I have a table set up with the structure below and created a couple of test pages (also below). After two frustrating weeks I still can't get it to function properly. The session_set_save_handler will write to MySQL but it writes 3x as 3 separate records and the session data isn't available on subsequent pages.
Debugging Steps I Tried:
Changed the function names to see if the underscore was causing a problem
Verified permissions to make sure the db user can perform all of the necessary functions (open, select, replace, delete, close)
Added an echo as the last step to each function (before return statements) to see when each function was executed
Moved session_start() from the end of session_handler.php file to the first thing that appears on each of the test pages (tried before the DTD and right after with <?php session_start(); ?>)
Commented out some of the mysql_real_escape_string() functions to see if they were somehow interfering
Reviewed the php error log to see if any problems were listed
Made sure the session handler directive in php.ini was set to files
Table Structure for Storing Session Data
id varchar(32) utf8_general_ci NotNULL NoDefault
access int(10) unsigned NULL DefaultNULL
data text utf8_general_ci NULL DefaultNULL
Session Set Save Handler File - session_handler.php
<?php
function _open() {
global $_sess_db;
if($_sess_db = mysql_connect('localhost', 'username_here', 'pw_here')) {
echo 'Open function was executed' . '<br />';
return mysql_select_db('db_name_here', $_sess_db);
}
return FALSE;
}
function _close() {
global $_sess_db;
echo 'Close function was executed' . '<br />';
return mysql_close($_sess_db);
}
function _read($id) {
global $_sess_db;
$sql = "SELECT data FROM sessions_table WHERE id = '".$id."'";
echo $sql . '<br />';
if($result = mysql_query($sql, $_sess_db)) {
if(mysql_num_rows($result)) {
$record = mysql_fetch_array($result);
echo 'Read function was executed' . '<br />';
return $record['data'];
}
}
echo 'Read function was executed 2nd Instance' . '<br />';
return '';
}
function _write($id, $data) {
global $_sess_db;
$access = time();
$data = mysql_real_escape_string($data);
$sql = "REPLACE INTO sessions_table VALUES ('".$id."','".$access."','".$data."')";
echo $sql . '<br />';
echo 'Write function was executed' . '<br />';
return mysql_query($sql, $_sess_db);
}
function _destroy($id) {
global $_sess_db;
$sql = "DELETE FROM sessions_table WHERE id = '".$id."'";
echo 'Destroy function was executed' . '<br />';
return mysql_query($sql, $_sess_db);
}
function _clean($max) {
global $_sess_db;
$old = time() - $max;
$old = mysql_real_escape_string($old);
$sql = "DELETE FROM sessions_table WHERE access < '".$old."'";
echo 'Clean function was executed' . '<br />';
return mysql_query($sql, $_sess_db);
}
session_set_save_handler('_open', '_close', '_read', '_write', '_destroy', '_clean');
session_start();
?>
Test Page 1 - Submitted with http://www.mydomain.com?aid=675 - session-test.php
<?php
require 'session_handler.php';
if(isset($GET['aid'])) {
$SESSION['activity_no'] = $GET['aid'];
}
else {
$SESSION['activity_no'] = 'Not Set';
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Sessions Test Page</title>
</head>
<body>
<?php
echo '<h1>Testing Set Session Save Handler</h1>';
$_SESSION['db_variable'] = 'Database Variable Value 1';
echo '<a href="session2-test.php">Click here</a> for Session Test page 2.';
?>
</body></html>
Test Page 2 - session2-test.php
<?php require 'session_handler.php'; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Session Test 2</title>
</head>
<body>
<?php
echo '<h1>Testing Page 2 Session Set Save Handler</h1>';
echo 'Database Variable from Session Array - Set on Test Page 1: ' . $_SESSION['db_variable'] . '<br />';
echo 'Activity Number from Session Array - Set on Test Page 1 if passes the is set function: ' . $_SESSION['activity_no'] . '<br />';
$db_var = '2nd session test variable';
$_SESSION['dbv2'] = $db_var;
echo 'Verify 2nd session variable was set with echo: ' . $_SESSION['dbv2'] . '<br />';
echo '<a href="session3-test.php">Next page</a> to test 3rd write.';
?>
</body>
</html>
Test Page 3 - session3-test.php
<?php require 'session_hanlder.php'; ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Session Test 3</title>
</head>
<body>
<?php
echo '<h1>Page 3 for Testing Our Session Set Save Handler</h1>';
echo 'DB Variable from Session Array: ' . $SESSION['db_variable'] . '<br />';
echo 'Activity Number from Session Array - if set with url when accessing first page: ' . $SESSION['activity_no'] . '<br />';
echo '2nd Variable from Session Array - set on page 2: ' . $_SESSION['dbv2'] . '<br />';
?>
</body>
</html>
I'm really at a loss. We host the site on a VPS running Linux with the latest stable versions of PHP and MySQL.
Thank you in advance to anyone that can help.
