2

I'd like to perform the following query:

    SELECT
      *,
      (SELECT COUNT(*) FROM `tab2` WHERE `parent` = :id) AS `sum`
    FROM `tab1`
    WHERE `id` = :id

As you can see :id placeholder appeared twice in the query. So if I'd try to execute this statement with:

$q->execute(['id'=>$row_id]);

I'm receiving the error:

Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number

So I have to rewrite the prepared query and execute array with :id1 and :id2 placeholders which looks a bit stupid for me.

Is it the only way to use one placeholder in several places of the prepared statement?

Vlada Katlinskaya
  • 991
  • 1
  • 10
  • 26
  • @DarkBee *Sort of*. That link that you posted as a possible duplicate, is using `SELECT :Param`. The initial problem is that the OP was trying to bind a column. This question http://stackoverflow.com/q/182287/ explains and specifies *"Table and Column names cannot be replaced by parameters in PDO"*, from the answer http://stackoverflow.com/a/182353/ on the same page. – Funk Forty Niner Dec 13 '14 at 17:59
  • @Fred-ii- true, but the answer that is marked on that page states that a placeholder only can be used once. – DarkBee Dec 13 '14 at 18:11
  • @Fred-ii- I'm surprised. Why is there such limitation?! Is it MySQL limitation? – Vlada Katlinskaya Dec 13 '14 at 18:17
  • @Fred-ii- I think that it's not a reason to turn on PDO::ATTR_EMULATE_PREPARES? – Vlada Katlinskaya Dec 13 '14 at 18:18
  • @DarkBee Yes, I agree and that is why my comment started with "Sort of" ;) – Funk Forty Niner Dec 13 '14 at 18:46
  • Cant really understand `SELECT SUM(*) FROM tab2` ? – Abhik Chakraborty Dec 13 '14 at 18:51
  • @VladaKatlinskaya The thing is about not being able/allowed to bind a table/column, is that MySQL doesn't know "ahead of time" what it is that it's supposed to bind (`:value="something";` is invalid). That's why using pre-defined variables, is a way around it. A bind is associated with a pre-defined variable or POST/GET variable, so now it knows what to bind, because the variable(s) have already been assigned ahead of time. So, doing `$value = "column"; SELECT $value` is allowed, but `SELECT :value` is not. – Funk Forty Niner Dec 13 '14 at 19:07
  • @AbhikChakraborty you're right - that's not a good example. I changed the syntax in the OT – Vlada Katlinskaya Dec 14 '14 at 16:41

1 Answers1

6

PDO::prepare states that

[y]ou cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

Since it's generally better to leave emulation mode off (so the database does the prepared statement), you'll have to use id_0, id_1, etc.

T0xicCode
  • 4,583
  • 2
  • 37
  • 50
  • 2
    Unless the statement is executed multiple times using different values, real prepared statements are not necessarily better. – Gumbo Dec 14 '14 at 16:52
  • Not necessarily better, but they offload the parameter parsing/quoting logic to the database, which is often faster than PHP (at least for those tasks). – T0xicCode Dec 14 '14 at 17:10
  • There is no parameter quoting when using real prepared statements. – Gumbo Dec 14 '14 at 17:31