1

I'm trying to switch to pdo and have enjoyed success with it but my latest function will not work. When I revert to mysqli, however, it is fine. I'm sure I'm missing something, but what??

not working PDO:

$db = db_pdo();
$query = $db->prepare("select * from locks_for_sale where type_3=':search'");
$query->bindParam(":search", $sub_items[3]);
$query->execute();

if (!$result=$query->fetch()) {
    $print .= "<tr><td>&nbsp;</td><td><h3>No products currently available.</h3></td></tr>\n";
}

else {
    other code

Please note:
function db_pdo is included.
$sub_items[3] is a string.

working mysqli:

$db = db_conn();
$sql = "select * from locks_for_sale where type_3='".$sub_items[3]."'";
$query = $db->query($sql);

if (!$query->fetch_assoc()) {
    $print .= "<tr><td>&nbsp;</td><td><h3>No products currently available.</h3></td></tr>\n";
}

else {
    other code

Again db_conn is included.

I know that the result of this query is returning 2 items but the pdo version shows !$result.

Thanks in advance.

dev-null
  • 551
  • 1
  • 4
  • 13
  • 1
    What does `var_dump($query->errorInfo()` say? Returning false indicates an error, so have PDO tell you exactly what the error is. – Marc B Feb 09 '12 at 21:54
  • @Marc B the output is `array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL } array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL }` – dev-null Feb 09 '12 at 22:04

3 Answers3

2

Remove the quotes from the :search param:

$query = $db->prepare("select * from locks_for_sale where type_3=:search");
//--------------------------------------------------------------^^^^^^^^^^

If quoted, it would be treated as a literal string ':search', and you'll wind up with an error for binding an incorrect number of parameters.

If you are new to prepared statements, try not to think of them as the equivalent of placing variables into a SQL string as you would when concatenating or interpolating variables. Instead you are passing the parameter values directly into the database engine, which will in turn place them accordingly into the query. It becomes the RDBMS' responsibility to construct a valid statement with appropriate quoting.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • when I do as you say I get nothing on screen which makes me think the problem might be further on in my code. Having said that, why would mysqli work and pdo not? – dev-null Feb 09 '12 at 22:06
  • MySQLi works because you aren't using prepared statements, but rather inserting the value directly into the SQL string. It's really a different kind of thing. `var_dump($result)` to see what you've got back from the fetch call. – Michael Berkowski Feb 10 '12 at 03:45
  • My PDO code was not working because of the quotes, like you said, but the whole script failed after I unquoted because a later function was using mysqli. I didn't realise because php.ini isn't set to echo errors in the browser and I only saw it through the command line. A basic set up error really. Thanks. – dev-null Feb 10 '12 at 22:18
0

Given your 00000 error code, that actually means the query succeeded. The problem is with your fetch logic:

if (!$query->fetch_assoc()) {
    ^--- remove

if the fetch succeeds, the call will return not-false, aka true. You then invert that with ! and turn it into a false, causing the other code to run.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • We know that the result of the query is true, regardless of it's values. Surely a legitimate test of this is to ask if the result of the query is not-true? That's why I put the `!` at the start to move on from any querys that are false, or not-true. If it is true, which it should be and it is in mysqli, then the code should process the `else {` part of the code, shouldn't it? – dev-null Feb 09 '12 at 22:53
  • sure, but the problem is your code ordering. if you inverse the sense of the if() test, you have to reverse the order of the true/false blocks. your code boils down to "if query failed, then do sucess stuff else do failure stuff". – Marc B Feb 10 '12 at 03:44
  • Thanks for the reply and thoughts on error checking/code ordering. I always thought the fetch was correct but it's nice to confirm it with errorInfo, something I'll be doing in the future. Thanks. – dev-null Feb 10 '12 at 22:23
0

try bindValue (http://www.php.net/manual/en/pdostatement.bindvalue.php)

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);
$sth->execute();
?>

See also: What is the difference between bindParam and bindValue?

Community
  • 1
  • 1
frugi
  • 605
  • 7
  • 26