PDO Statement problems

[imperium2335]

Hi,

 

I have the following script:

if(stristr($_SERVER['HTTP_REFERER'], 'chat-face.php')) {

$roomId = 0 ;

try {
$connection = new PDO('mysql:host=localhost;dbname=xxxxxxx', 'xxxxx', 'xxxxxx');
}
catch(Exception $e) {
$_SESSION['fatal'] = 'We are currently undergoing maintenance, and will be back shortly.(E:0)' ;
header("Location: index.php") ;
}

$query = "SELECT roomRef, username, message
				   FROM (SELECT public_logs.*, users.username
				   		 FROM public_logs, users
				   		 WHERE roomRef = :roomid
						 AND public_logs.sendingUser = users.id
				   		 ORDER BY date DESC
				   		 LIMIT 26)
				   AS tbl
				   ORDER BY tbl.date" ;

$result = $connection->query($query) ;
$result->setFetchMode(PDO::FETCH_ASSOC) ;
while($row = $result->fetch()) {

echo $row['username'] . " says: " . stripslashes($row['message']) . '<br />' ;

}
}

 

I get the error: Fatal error: Call to a member function setFetchMode() on a non-object in W:\xampp\htdocs\test\test2\fetch-chat.php on line 26

 

Isn't it an object though because $connection is an object and $result = $connection so therefore $result must be an object too???

 

I've followed some tutorials but it just doesn't work for me. I'm new to PDO and trying to learn it to replace my use of the 'old' sql syntax because everyone says its better and more secure (is that really true?).

[JasonLewis]

PDO::query() returns a statement object if the query was successful, or false if it failed. Perhaps your query is failing?

 

I've heard rumors about the deprecation of the MySQL extension for PHP (probably not for a while) in favor of the other (better) extensions such as MySQLi and the PDO abstraction layer. They are better. They offer a heap of great features and promote reusable code. Plus it's based around objects.

[imperium2335]

Should the variable :roomid be enclosed in 's?

[imperium2335]

I did that and now I'm getting:

Fatal error: Call to a member function fetch() on a non-object in W:\xampp\htdocs\Chat\system\fetch-chat.php on line 27

[imperium2335]

Seems I fixed it, here it is incase its of any use to anyone else:

 

$query = "SELECT roomRef, username, message
				   FROM (SELECT public_logs.*, users.username
				   		 FROM public_logs, users
				   		 WHERE roomRef = ':roomid'
						 AND public_logs.sendingUser = users.id
				   		 ORDER BY date DESC
				   		 LIMIT 26)
				   AS tbl
				   ORDER BY tbl.date" ;

$stmnt = $connection->query($query) ;
$stmnt->bindParam(':roomid', $roomId);
//$result = $stmnt->setFetchMode(PDO::FETCH_ASSOC) ;
while($row = $stmnt->fetch(PDO::FETCH_ASSOC)) {

echo $row['username'] . " says: " . stripslashes($row['message']) . '<br />' ;

}
}

[JasonLewis]

I believe you should be using PDO::prepare() and not PDO::query(), also no need to enclose your placeholder in single quotes.

 

PDO::query() executes the query straight away so I don't see how binding the parameter after this would help. Although I haven't used PDO in a while, so I could be wrong.

[imperium2335]

So the PDO knows that anything with : infront of it is a variable? How else could you assign a variable to it without using bindParam? Does that automatically escape the variable if $roomId were user input?

 

$stmnt->bindParam(':roomid', $roomId);

[imperium2335]

In the PHP manual, it has the following:

 

$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

$sth->execute(array(':calories' => 150, ':colour' => 'red'));

 

The only bit I really don't get is the (PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY) part. Could someone explain what this does?

 

Am I right to guess that this basically forces the mysql result pointer to go only forward, but wouldn't it do that anyway?

[JasonLewis]

Actually the colon in front of the word just makes referencing it a whole lot easier. I believe you could use whatever you wanted. If you check out the manual, it explains the difference between bindParam and bindValue.

Example:

 

$roomId = 5;

// Do PDO connection stuff here and prepare your query.
$stmnt->bindParam(':roomid', $roomId);

$roomId = 6; // This actually changes the bound parameter (:roomid) to 6, as the variable was bound by reference so any changes made to the original are reflected here.

 

bindValue() on the other hand simply binds the variable without referencing it.

The data you bind is escaped, and it's recommended you declare the type of data your are expecting with the 3rd parameter.

 

In answer to your other question, about what the manual says, I've never used the driver options so am unable to explain the meaning of them.

[imperium2335]

Hi, if you are able to change the variable through a reference, then the script is not linear right? Thanks for your help so far!

[JasonLewis]

Well, the purpose of binding variables by reference is so you don't need to run the entire query again to set the variables again. You can simply prepare your query, bind the parameters and set them, then execute the query, update the variables and execute the query again.

 

$sth = $db->prepare("SELECT foo FROM bar WHERE id=:id");
$sth->bindParam(':id', $id, PDO::PARAM_INT);

$id = 2;
$results[] = $sth->execute();

$id = 4;
$results[] = $sth->execute();

 

Of course it's more commonly used when inserting or updating data, not selecting. But you get the idea. But again, I'm a bit rusty with PDO. Hopefully this is somewhat helpful though.

[imperium2335]

Thanks for explaining this, I have it working well now

 

For the purpose of my script, it is called by ajax every .5 seconds, so do you think that I should use a persistent connection for this script?

[JasonLewis]

Here is a better answer then what I could ever come up with: http://stackoverflow.com/questions/3332074/what-are-the-disadvantages-of-using-persistent-connection-in-pdo

[imperium2335]

Thanks! I think I will play safe and stick with closing connections after the select is done and call $connection = NULL ; at the end.

[imperium2335]

I've been reading that PDO is immune to SQL Injection attacks.

 

Is this true of my following routine?

 

$stmnt = $connection->prepare("INSERT INTO public_logs (roomRef, sendingUser, date, message) VALUES(?, ?, NOW(), ?)") ;
$stmnt->bindValue(1, $roomId, PDO::PARAM_INT) ; // make first ? the roomid, force to integer.
$stmnt->bindValue(2, $userId, PDO::PARAM_INT) ;
$stmnt->bindValue(3, $_POST['string'], PDO::PARAM_STR) ;
$stmnt->execute() ;

[TeNDoLLA]

Yes.