PHP PDO Session Class using Stored Procedures in MySQL

[Coyote6]

I am trying to create a class using a PDO connection from another file to store my session data inside my MySQL database using stored procedures. I can start the session and it will write to the database but then when a new page is loaded I lose all of my session data. I read somewhere about the __sleep and __wakeup function possibly solving the issue but I have no idea how I would implement that into this if that is the case. Also I tried the insert and update method but I commented that out and tried using replace since my old non-class and non-pdo version used it. But that ended with the same results. Any help would be greatly appreciated.

DB is my PDO class for the database.

My DB uses the table sess with fields sess_id, sess_access, sess_data.

Here are my stored procedures.

-- Select the session data.
CREATE PROCEDURE `get` (IN in_sess_id VARCHAR(32), IN in_time INT(10))
BEGIN
SELECT sess_data FROM sess WHERE sess_id=in_sess_id and sess_access>in_time;
END$$

-- Write the session data.
CREATE PROCEDURE `write` (IN in_sess_id VARCHAR(32), IN in_time INT(10), IN in_data TEXT)
BEGIN 
REPLACE INTO sess VALUES (in_sess_id, in_time, in_data);
END$$

-- Write the session data.
CREATE PROCEDURE `insert` (IN in_sess_id VARCHAR(32), IN in_time INT(10), IN in_data TEXT)
BEGIN 
INSERT INTO sess (sess_id, sess_access, sess_data) VALUES (in_sess_id, in_time, in_data);
END$$

-- Update the session data.
CREATE PROCEDURE `update` (IN in_sess_id VARCHAR(32), IN in_time INT(10), IN in_data TEXT)
BEGIN 
UPDATE sess SET sess_access=in_time, sess_data=in_data WHERE sess_id=in_sess_id;
END$$

-- Delete the session data.
CREATE PROCEDURE `destroy` (IN in_sess_id VARCHAR(32))
BEGIN
DELETE FROM sess WHERE sess_id=in_sess_id;
END$$

-- Clean out old session data.
CREATE PROCEDURE `clean` (IN in_time INT(10))
BEGIN
DELETE FROM sess WHERE sess_access<in_time;
END$$

PHP Code:

<?php

class Session {

// Variables.
private $max_session = 86400; // One day.

// Close session.
public function __destruct() {
session_write_close();
}

// Check to see if a connection is made to the db, start one if not.
public function open () {
return true;
}

// Close the dbc for the session.
public function close () {
return true;
}

// Pull the data from the db to read.
public function read ($id) {
global $max_session;
$old = DB::escape_data(time() - $max_session);
$q = "CALL get (:id, :old)";
$p = array ();
$p[':id'] = $id;
$p[':old'] = $old;
return DBC::get_row($q, $p);
}

// Write the session info into the db.
public function write ($id, $data) {
$time = DB::escape_data (time());
$id = DB::escape_data ($id);
$data = DB::escape_data ($data);
$q = "CALL write (:id, :time, :data)";
$p = array ();
$p[':id'] = $id;
$p[':time'] = $time;
$p[':data'] = $data;
return DB::execute($q, $p);
/*try {
$q = "CALL insert (:id, :time, :data)";
$p = array ();
$p[':id'] = $id;
$p[':time'] = $time;
$p[':data'] = $data;
DB::execute($q, $p);
}
catch (PDOException $e) {
$q = "CALL update (:id, :time, :data)";
$p = array ();
$p[':id'] = $id;
$p[':time'] = $time;
$p[':data'] = $data;
DB::execute($q, $p);
}*/
}

// Destroy session data in db.
public function destroy ($id) {
$q = "CALL destroy (:id)";
$p = array ();
$p[':id'] = $id;
return DB::execute($q, $p);
}

// Clean out the old sessions.
public function clean ($max_session) {
global $max_session;
$old = DB::escape_data(time() - $max_session);
$q = "CALL clean (:old)";
$p = array ();
$p[':old'] = $old;
return DB::execute ($q, $p);
}

}
// Call the session handler and start the session.
$session = new Session;
session_set_save_handler (array (&$session, "open"), array (&$session, "close"), array (&$session, "read"), array (&$session, "write"), array (&$session, "destroy"), array (&$session, "clean"));
session_start ();

?>

Thanks for and ideas in advance.

[Coyote6]

Okay after days of banging my head against the wall I figured it out. I created a constructor class to hold the handler but do not place the session start in there. Then I got rid of all of my escaped data it was escaping my quotes I needed. The main thing was it was not accessing my max_lifetime variable for some reason so I hard coded the time into the function... Probably could replace with a constant. The replace method still worked so I did not do the insert/update method for insert the session info.

PHP Code:

<?php 

class Session { 
 
// Set the session handler when called.
public function __construct () {
      session_set_save_handler (array (__CLASS__, "open"), array (__CLASS__, "close"), array (__CLASS__, "read"), array (__CLASS__, "write"), array (__CLASS__, "destroy"), array (__CLASS__, "clean"));
}

// Close session. 
public function __destruct() { 
      session_write_close(); 
} 

// Check to see if a connection is made to the db, start one if not. 
public function open () { 
      return true; 
} 

// Close the dbc for the session. 
public function close () { 
      return true; 
} 

// Pull the data from the db to read. 
public function read ($id) { 
      $old = time() - 864000; 
      $q = "CALL get (:id, :old)"; 
      $p = array (); 
      $p[':id'] = $id; 
      $p[':old'] = $old; 
      return DBC::get_row($q, $p); 
} 

// Write the session info into the db. 
public function write ($id, $data) { 
      $time = time(); 
      $id = $id; 
      $data = $data; 
      $q = "CALL write (:id, :time, :data)"; 
      $p = array (); 
      $p[':id'] = $id; 
      $p[':time'] = $time; 
      $p[':data'] = $data; 
      return DB::execute($q, $p); 
} 

// Destroy session data in db. 
public function destroy ($id) { 
     $q = "CALL destroy (:id)"; 
     $p = array (); 
     $p[':id'] = $id; 
     return DB::execute($q, $p); 
} 

// Clean out the old sessions. 
public function clean ($max_session) { 
     $old = time() - 864000; 
     $q = "CALL clean (:old)"; 
     $p = array (); 
     $p[':old'] = $old; 
     return DB::execute ($q, $p); 
} 

} 
// Call the session handler and start the session. 
new Session; 
session_start (); 

?>

Enjoy everyone!