Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Sessions mysql deprecated to PDO conversion queries issues

mysqli and pdo

This topic has been archived. This means that you cannot reply to this topic.
7 replies to this topic

#1 mutago

mutago

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 478 posts

Posted 24 September 2013 - 07:06 AM

The code below works fine using mysql deprecated but when i tried to move it to PDO it displays the following error

Notice: Undefined variable: db in C:\xampp\htdocs\copytimeline\timeline1\session.php on line 36

Fatal error: Call to a member function query() on a non-object in C:\xampp\htdocs\copytimeline\timeline1\session.php on line

 

can someone help me out

 

working.php

 


<?php
  class SessionDB {

    private $data=null;
    private $session_id=null;
    private $minutes_to_expire=3600; // TIME TO MAINTAIN DATA ON DB
    
    public function __construct(){
      global $SESSION;
      
      if (isset($_COOKIE['session_id'])){
        $this->session_id = $_COOKIE['session_id'];
      } else {
        
        $this->session_id = md5(microtime().rand(1,9999999999999999999999999)); // GENERATE A RANDOM ID
        
        setcookie('session_id',$this->session_id);
        
        $sql = "INSERT INTO `tb_session_db` (`session_id`, `updated_on`) VALUES ('{$this->session_id}', NOW())";
        mysql_query($sql);
      }
      
      $sql = "SELECT `value` FROM `tb_session_db` WHERE `session_id`='{$this->session_id}'";
      $query = mysql_query($sql);
      
      $this->data = unserialize(mysql_result($query, 0, 'value'));
      $SESSION = $this->data;
    }
    
    private function expire(){
      $date_to_delete = date("Y-m-d H:i:s", time()-60*$this->minutes_to_expire);
      $sql = "DELETE FROM `tb_session_db` WHERE `update_on` <= '$date_to_delete'";
      mysql_query($sql);
    }
    
    public function __destruct(){
      global $SESSION;
      
      $this->data = serialize($SESSION);
      
      $sql = "UPDATE `tb_session_db` SET `value`='{$this->data}', `updated_on`=NOW() WHERE `session_id`='{$this->session_id}'";
      mysql_query($sql);
      
      $this->expire();
    }
  }
 
?>
 

 

PDO.php

<?php

  class SessionDB {

    private $data=null;
    private $session_id=null;
    private $minutes_to_expire=3600; // TIME TO MAINTAIN DATA ON DB 3600 sec or 1 hour
    
    public function __construct(){
      global $SESSION;
      
      if (isset($_COOKIE['session_id'])){
        $this->session_id = $_COOKIE['session_id'];
      } else {
        
        //$this->session_id = md5(microtime().rand(1,9999999999999999999999999)); // GENERATE A RANDOM ID
        
        $this->session_id = md5(microtime().rand(1,999999999999)); // GENERATE A RANDOM ID


        setcookie('session_id',$this->session_id);
        
$statement = $db->prepare('INSERT INTO tb_session_db ( session_id,updated_on )
                          values
                ( :session_id,:NOW())');

         $statement->execute(array(
            ':session_id' => $this->session_id,
                         ':NOW()'=> NOW()
            ));

                $query = $result->fetch();

      }         
     
     $result = $db->query('
            SELECT value FROM tb_session_db
            WHERE session_id = :session_id
            
        ');
        $result->execute(array(
            ':session_id' => $this->session_id
            ));
$query = $result->fetch();

      $this->data = unserialize(pdo_result($query, 0, 'value'));
      $SESSION = $this->data;
    }
    private function expire(){
      $date_to_delete = date("Y-m-d H:i:s", time()-60*$this->minutes_to_expire);
      

$result = $db->query('
            DELETE FROM tb_session_db
            WHERE update_on <= :update_on
            
        ');
        $result->execute(array(
            ':update_on' => $date_to_delete
            ));
$query = $result->fetch();
    }
    
    public function __destruct(){
      global $SESSION;
      
      $this->data = serialize($SESSION);
      
$update = $db->prepare('
            UPDATE tb_session_db SET
            value = :value, updated_on = :updated_on
            WHERE session_id= :session_id');
        $update->execute(array(
            ':value' => $this->data,
                      ':updated_on' => NOW(),
            ':session_id' => $this->session_id));
$query = $update->fetch();
      
      $this->expire();
    }
  }
 
?>
 
 

 

database handler

 
<?php
   require_once('pdo.php');
    
$db = new PDO (
    'mysql:host=localhost;dbname=timeline',
    'root', // username

    '' // password
);

    

$SESSION = null;
  global $SESSION;
  $session_db = new SessionDB();  
?>
 
 

 

 

 



this is where the error is coming from

 

$result = $db->query('
            SELECT value FROM tb_session_db
            WHERE session_id = :session_id
            
        '
);
        $result->execute(array(
            ':session_id' => $this->session_id
            
));



#2 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 24 September 2013 - 08:35 AM

In the pdo version, $db is not initialised.

When you call $session_db = new SessionDB(); pass the variable $db as a parameter and save it in sessionDB constructor.


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#3 mutago

mutago

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 478 posts

Posted 25 September 2013 - 06:23 AM

I tried the code as follows but it reported the errors below


Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\xampp\htdocs\copytimeline\timeline1\session.php on line 39

Warning: mysql_result() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\copytimeline\timeline1\session.php on line 43

I think my insert statement has problem as column value is not updated when using PDO


my table was created as follows

  CREATE TABLE IF NOT EXISTS `tb_session_db` (
    `session_id` varchar(32) NOT NULL,
    `value` blob,
    `updated_on` datetime DEFAULT NULL,
    PRIMARY KEY (`session_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
So when i got to database,it seems that no records were inserted into column value.

what i found on the column value was [BLOB - 0B] instead of something like [BLOB - 4B] etc

Can you help me please
below is the code

<?php
  class SessionDB {

    private $data=null;
    private $session_id=null;
    private $minutes_to_expire=3600; // TIME TO MAINTAIN DATA ON DB 3600 sec or 1 hour
    
    public function __construct($db){
      global $SESSION;
      
      if (isset($_COOKIE['session_id'])){
        $this->session_id = $_COOKIE['session_id'];
      } else {
        
        $this->session_id = md5(microtime().rand(1,9999999999999999999999999)); // GENERATE A RANDOM ID
        
       // $this->session_id = md5(microtime().rand(1,999999999999)); // GENERATE A RANDOM ID


        setcookie('session_id',$this->session_id);
        
$statement = $db->prepare('INSERT INTO tb_session_db ( session_id,updated_on)
                          values
                ( :session_id,NOW())');

         $statement->execute(array(
            ':session_id' => $this->session_id
            ));

                //$query = $statement->fetch();
      }         

$result = $db->prepare("SELECT value FROM tb_session_db
            WHERE session_id = :session_id");

//$result->bindParam(':session_id', $this->session_id, PDO::PARAM_STR);
$result->bindParam(1, $this->session_id, PDO::PARAM_STR);
$result->execute();

$query = $result->fetch();

      $this->data = unserialize(mysql_result($query, 0, 'value'));
      $SESSION=$this->data;
    }
    private function expire(){
      $date_to_delete = date("Y-m-d H:i:s", time()-60*$this->minutes_to_expire);
      
$result = $db->query('
            DELETE FROM tb_session_db
            WHERE update_on <= :update_on
            
        ');
        $result->execute(array(
            ':update_on' => $date_to_delete
            ));
$query = $result->fetch();
    }
    
    public function __destruct(){
      global $SESSION;
      
      $this->data = serialize($SESSION);
      
$update = $db->prepare('
            UPDATE tb_session_db SET
            value = :value, updated_on = :updated_on
            WHERE session_id= :session_id');
        $update->execute(array(
            ':value' => $this->data,
                      ':updated_on' => NOW(),
            ':session_id' => $this->session_id));
$query = $update->fetch();
      
      $this->expire();
    }
  }
 
?>


#4 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 25 September 2013 - 07:56 AM

When you are repporting error with a line number, please highlight that line, it's a lots easier

 

But I think your error is

$result = $db->prepare("SELECT value FROM tb_session_db
            WHERE session_id = :session_id");

//$result->bindParam(':session_id', $this->session_id, PDO::PARAM_STR);
$result->bindParam(1, $this->session_id, PDO::PARAM_STR);

bindParam with a number is when you use nameless placeholder (when you use ? in your query).

Change the 1 with ':session_id' it should work... and you are sure an id is a PDO::PARAM_STR type?


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#5 mutago

mutago

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 478 posts

Posted 25 September 2013 - 12:14 PM

One of the problem solved. Am still having one error

 

Warning: mysql_result() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\copytimeline\timeline1\session.php on line 43

 

This error is from this line of code

 

$this->data = unserialize(mysql_result($query, 0, 'value'));
      $SESSION=$this->data;
 

 

I think my insert statement above has problem as nothing was inserted in column value in the database and thus nothing is being queried in the SELECT statement which gives rise to the error above.

 

 

So when i got to database,it seems that no records were inserted into column value.

what i found on the column value was [BLOB - 0B] instead of something like [BLOB - 4B] etc

Thus column value has 0 for any user that logs in via session

 

If i used mysql_deprecated, the value column gets updated automatically



#6 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 25 September 2013 - 03:02 PM

Why are you mixing pdo and mysql_*?

 

Anyway try to var_dump of mysql_error, it will tell you what is the error.


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !


#7 mutago

mutago

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 478 posts

Posted 25 September 2013 - 09:15 PM

when i var_dump it as follows

 

      $this->data = unserialize(var_dump($query, 0, 'value'));
      $SESSION=$this->data;
 
 

I got this error

 

 

array(2) { ["value"]=> NULL [0]=> NULL } int(0) string(5) "value"



#8 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts

Posted 26 September 2013 - 02:44 AM

I mean, there is a command mysql_error http://php.net/manua...mysql-error.php

Do a var_dump of this, it will help us.


You can now stalk me on linkedin: http://ca.linkedin.c...elle/24/b44/88/ !





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download