14 Replies - 728 Views - Last Post: 27 September 2013 - 01:43 AM Rate Topic: -----

#1 mutago234  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 166
  • Joined: 08-September 13

Sessions mysql deprecated to PDO conversion queries issues

Posted 24 September 2013 - 08:10 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
            ));



Is This A Good Question/Topic? 0
  • +

Replies To: Sessions mysql deprecated to PDO conversion queries issues

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 24 September 2013 - 09:33 AM

Well, the error pretty much says it all - $db is not defined. You created it you "database handler" but you never bring it into scope in your class methods. You would need to reference it with a global $db declaration in each method to make that code work.

Of course, using global variables isn't such a great practice in the first place (maybe use a singleton or something instead), but that's a different issue.
Was This Post Helpful? 0
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 24 September 2013 - 02:09 PM

One thing to keep in mind when converting deprecated MySQL API code to PDO is that the old mysql_connect method opened a more or less "global" connection that would automatically be used by any mysql_connect call that didn't specify an alternative, regardless of where in the code the call is made.

PDO doesn't do that. It exists as an object reference that needs to be passed - one way or another - to the parts of the code that need them.

AdaHacker mentioned Singleton classes, and I've found that to be one of the better ways to handle database connections in PHP, unless the code is bootstrapped in some other more specific way. I highly suggest looking into that. It's not an overly complicated pattern, so that's a plus, and you'll no doubt be able to find numerous examples of it being used for just this purpose all over the internet. (I've even posted a few examples on DIC myself, though I can't remember where. Google can no doubt dig those up if you ask it nicely.)
Was This Post Helpful? 0
  • +
  • -

#4 mutago234  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 166
  • Joined: 08-September 13

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 24 September 2013 - 03:28 PM

Thank you. I will try it and get back to you.
Was This Post Helpful? 0
  • +
  • -

#5 mutago234  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 166
  • Joined: 08-September 13

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 07:27 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(); 
    } 
  } 
  
?> 



Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3623
  • View blog
  • Posts: 12,511
  • Joined: 12-December 12

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 07:34 AM

Where are lines 39 and 43 in your posted code?
Was This Post Helpful? 0
  • +
  • -

#7 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 07:43 AM

Binding to a question mark placeholder doesn't work when you are using a named placeholder.
// Won't work.
$sql = "SELECT ... WHERE field = :item";
$stmt = $bind->prepare($sql);
$stmt->bindValue(1, "value");

// Will work.
$sql = "SELECT ... WHERE field = :item";
$stmt = $bind->prepare($sql);
$stmt->bindValue(":item", "value");



Quote

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

You are using PDO now. There shouldn't be any mysql_* function calls.
Was This Post Helpful? 0
  • +
  • -

#8 mutago234  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 166
  • Joined: 08-September 13

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 01:44 PM

One of the problem solved. Am still having one error

Quote

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
Any help

This post has been edited by andrewsw: 25 September 2013 - 01:58 PM
Reason for edit:: Tidied whitespace

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3623
  • View blog
  • Posts: 12,511
  • Joined: 12-December 12

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 01:56 PM

Echoing Atli 's point in his previous post, you are using PDO so you shouldn't still have any mysql_ function calls in your code. You need to replace all of these calls.
Was This Post Helpful? 0
  • +
  • -

#10 mutago234  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 166
  • Joined: 08-September 13

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 02:22 PM

what should replace it. can give example.

thanks
Was This Post Helpful? 0
  • +
  • -

#11 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 02:34 PM

Think about it for a second, before you start asking for code. What exactly is the purpose of mysql_result within the old-school MySQL API? Do you know what it does; why it was used?
Was This Post Helpful? 0
  • +
  • -

#12 mutago234  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 166
  • Joined: 08-September 13

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 04:17 PM

To print an error message if value was set to 0
Was This Post Helpful? 0
  • +
  • -

#13 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 04:25 PM

No, that's not what it does. - Let me quote the doc entry I linked:

php.net/mysql_result said:

string mysql_result ( resource $result , int $row [, mixed $field = 0 ] )


Retrieves the contents of one cell from a MySQL result set.

Does that clear it up at all?

Knowing how to read the docs is very important. It's always the first place you should look when the purpose of a function call is uncertain. Without knowing what the function calls do, there is little hope of actually managing to write/rewrite anything.
Was This Post Helpful? 0
  • +
  • -

#14 mutago234  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 166
  • Joined: 08-September 13

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 25 September 2013 - 10:29 PM

please am not cleared
i don't know how to apply that code from the doc. Am new into PDO.
I think PDO is good but its headache is too much.

I try to var_dump as follow.


$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"
Was This Post Helpful? 0
  • +
  • -

#15 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3554
  • View blog
  • Posts: 10,335
  • Joined: 08-June 10

Re: Sessions mysql deprecated to PDO conversion queries issues

Posted 27 September 2013 - 01:43 AM

this is not an error, that’s the output of var_dump() (and yes, in the Manual is told why you see that)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1