Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts

    Question PHP PDO Session Class using Stored Procedures in MySQL

    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.
    Code:
    -- 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.

  • #2
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts

    Cool Resolved

    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!


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •