1

I've been playing around with switching over to the PDO way of doing database work in PHP. In my java life, I am able to place named queries into an associative array and call the prepared statement using the index. It's a bit more complex than that, but...

Anyways I thought it would be cool to do the same type of thing in PHP.

$NamedQueries['SelectBlackBoxById'] = "select name, category, rating from blackbox where id = :blackbox_id";

So I can prepare my statements this way:

$sth = $dbh->prepare($NamedQueries['SelectBlackBoxById']);
$sth->execute(array('blackbox_id' => '1'));
$sth->setFetchMode(PDO::FETCH_OBJ); 
return $sth->fetch();

Instead of this way:

$sth = $dbh->prepare("select name, category, rating from blackbox where id = :blackbox_id");
$sth->execute(array('blackbox_id' => '1'));
$sth->setFetchMode(PDO::FETCH_OBJ); 
return $sth->fetch();

I'm sure I am overlooking something, because my preferred way returns false. Any ideas would be greatly appreciated.

eterry28
  • 59
  • 5
  • At that point why not just use stored procedures? – Waleed Khan Feb 15 '13 at 17:05
  • What is the difference exactly? The only difference I see is that you are putting the query in a variable (works exactly the same...). Problem is that you are forgetting the `:`, so it should be `execute(array(':blackbox_id' => '1'));` and you're set. – Green Black Feb 15 '13 at 17:16
  • Always a possibility. I'd like to keep the logic in the code. – eterry28 Feb 15 '13 at 17:17
  • 2
    storing the queries is all fine and dandy... but then you STILL have to set up the parameters and know what they are in advance, so you really don't gain anything excepting having to look somewhere ELSE for the query text. PDO itself couldn't care less if the query string is coming from a hard-coded string or some an array elsewhere. – Marc B Feb 15 '13 at 17:20
  • 1
    PS, you can pass the fetch mode in the `$sth->fetch(PDO::FETCH_OBJ);` – crush Feb 15 '13 at 17:21
  • Marc - I agree that the queries will be elsewhere. As I said, I do something similar in java where I work. It's just another way of organizing the code. – eterry28 Feb 15 '13 at 17:31
  • My `$NamedQueries` array was out of scope. As @MarcB mentioned about having query text live elsewhere. Thanks to all of you for your input. – eterry28 Feb 15 '13 at 17:44

3 Answers3

0
$sth->execute(array('blackbox_id' => '1'));

Should be

$sth->execute(array(':blackbox_id' => '1'));

You have to include the :

crush
  • 16,713
  • 9
  • 59
  • 100
  • No you don't. This is not correct. I do it all the time without the `:` prefix. It makes it easy to pass $_POST to your execute() function (or a subset of $_POST). – Bill Karwin Feb 15 '13 at 18:15
  • @BillKarwin If you include the `:` in the query placeholder, then yes, you have to include it. – crush Feb 15 '13 at 18:18
  • 1
    Absolutely false. I just tested this the other day for a SO answer: http://stackoverflow.com/questions/14814592/warning-pdostatementexecute-sqlstatehy093-invalid-parameter-number-par/14818966#14818966 – Bill Karwin Feb 15 '13 at 18:18
  • @BillKarwin I just attempted this locally, and I get the following error `Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined` just saying. Also, from PHP's docs: `5.2.0 The keys from input_parameters must match the ones declared in the SQL. Before PHP 5.2.0 this was silently ignored.` found [here](http://php.net/manual/en/pdostatement.execute.php). – crush Feb 15 '13 at 18:25
  • I do see in user submitted examples where they are not prepending the `:`. PHP makes no note of this, and provides no example of this. Perhaps it was silently added in a later version as well? – crush Feb 15 '13 at 18:30
  • Fair enough, it may be another version-dependent change. I just tried it on PHP 5.3.15. I ran all the code samples from the PDOStatement::execute() doc page, with and without leading `:` in parameter array keys. It all worked fine. – Bill Karwin Feb 15 '13 at 18:36
  • So I retract my unequivocal statements, instead I'll say "false, on current versions of PHP." ;-) – Bill Karwin Feb 15 '13 at 18:36
  • @BillKarwin Thanks for pointing this out though. I've got some code I can go back and refactor now to be clearer! (after updating PHP) – crush Feb 15 '13 at 18:46
0

Did you try to dump out your Array value before you use it to prepare the query? Overall, I don't see anything wrong with how the PDO would use it, you could also var_dump() your $sth variable after you prepare it to see what might be in there.

In general, as others pointed out the ':' is something you should include when you bind variables, although in this case I don't necessarily think it is the root problem as you said your second example worked, which uses the same syntax, the only difference is using the query from the array instead of a raw string. Thinking that since it is only 1 variable to bind, not having the ':' is not causing problems (though you should add it)

Brad Bonkoski
  • 76
  • 5
  • It's probably erroring out because he didn't bind any variables, thus the query is failing, thus it is returning false when he tries to fetch on the `$stmt` object. It is erroring out because he did not bind the variable properly. PS, he didn't say the second way worked. He simply said the first way did not work. – crush Feb 15 '13 at 17:22
  • crush - thanks to you and John for pointing out the missing ':'. My second example did work though. – eterry28 Feb 15 '13 at 17:28
  • @brad thanks for pointing out the use of a simple var_dump(). Funny how such a little thing will help track down problems. – eterry28 Feb 15 '13 at 17:47
  • 1
    In fact, it's not necessarily to prefix parameter keys with ':' in PDO, only named parameter placeholders in the SQL string. – Bill Karwin Feb 15 '13 at 18:14
  • I actually *ran* the code php5.3.10, and it worked just fine with a valid query string and not using the ':' when binding the variable. – Brad Bonkoski Feb 15 '13 at 18:44
0

So, your real problem is lack of error handling. And it persists.

error_reporting(E_ALL);

to be notified of variables that out of scope.

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

after connect to be notified of empty queries and whatever else PDO errors

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345