1

I have lots of queries where a variable that is bound needs to be used more than once. Here's a simple example.

$stmt = $db->prepare('SELECT SUM(col1),

                      (SELECT SUM(col2)
                      FROM table2
                      WHERE col3 > :val) as quantity

                      FROM table1
                      WHERE col4 = :val');

When I do something like this, I always get the error:

Error!: SQLSTATE[HY093]: Invalid parameter number in ...

There are typically reasons why I can't do something like

WHERE col3 = col4

In other words, there are situations where I simply need to use a bound variable more than once. In the past, I've just bound the values multiple times with slightly different names.

Is it possible to use a bound variable more than once?

Nate
  • 26,164
  • 34
  • 130
  • 214
  • you can assign them same values but you do need to name them differently like so: col3 > :col3_val and col4 > :col4_val and then bind same values to :col3_val and :col4_val. – Maximus2012 Aug 09 '13 at 21:27
  • @Maximus2012 - that's what I've done in the past, but the reason I asked is that I'm working on a query right now that has a bunch of unrelated subqueries and unions and I would have to bind the same values tons of times, which just doesn't seem like a good way of doing things. – Nate Aug 09 '13 at 21:57
  • could you use array of bind variables or something like that ? – Maximus2012 Aug 09 '13 at 21:58
  • something like this maybe: http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition ? – Maximus2012 Aug 09 '13 at 21:59

1 Answers1

2

Either set PDO::ATTR_EMULATE_PREPARES to TRUE or use your "slightly different names" approach. It doesn't make too much difference though

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    with PDO::ATTR_EMULATE_PREPARES set to TRUE, can same bind variable be used multiple times in a query ? – Maximus2012 Aug 09 '13 at 22:00
  • Wow, it works! I've spent the last few minutes reading about the `ATTR_EMULATE_PREPARES` setting and I can't figure out why it fixes the problem. Is there any chance you could expound a little on your answer to say *why* it works? Thank you! – Nate Aug 09 '13 at 22:09
  • 1
    unfortunately it's too long a story. PHP team decided to make it this way to avoid some vague problems – Your Common Sense Aug 10 '13 at 05:51