1

I have some code

$query = 'INSERT INTO table
                  (foo, bar, baz)
                  VALUES
                  (:foo, :bar, :baz)
          ON DUPLICATE KEY UPDATE foo = :foo,
                                  bar = :bar,
                                  baz = :baz';
$stmt = $dbc->prepare($query);
$stmt->bindValue(':foo', $foo, PDO::PARAM_STR);
$stmt->bindValue(':bar', $bar, PDO::PARAM_STR);
$stmt->bindValue(':baz', $baz, PDO::PARAM_STR);

$stmt->execute();

Its throwing an error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Obviously, I have twice as many token as bound variables, but I have the same number of unique tokens. So my question is, can each token only be used once? Will I need to rename the second instance of each token to get it to work, or is there a way to do it without doubling my bindValue statements?

amflare
  • 4,020
  • 3
  • 25
  • 44

1 Answers1

5

It turns out that you can reuse tokens. My error was something else entirely. So if you find this in the future wondering the same thing, yes it's possible. Your error is from something else. Probably missed a colon or in your mess of tokens forgot to add a bindValue for one.

As noted by Barmar below, this functionality may need PDO::ATTR_EMULATE_PREPARES enabled to occur. MySQL has it enabled by default, though if using a framework such as Laravel, it may be turned off.

This answer has instructions on how to enable for Laravel.

amflare
  • 4,020
  • 3
  • 25
  • 44
  • I think this may only work if you have `PDO::ATTR_EMULATE_PREPARES` enabled. – Barmar Apr 19 '16 at 21:28
  • I've reopened the question. The line that the other question quoted from the documentation is no longer there, so apparently this has changed in PDO since then. – Barmar Apr 19 '16 at 21:29
  • @Barmar That may be true. I use MySQL which has that enabled by default. I suppose this is worth noting in the answer. Also thanks for reopening. – amflare Apr 19 '16 at 21:53
  • There's a comment in the linked page that makes the same observation. – Barmar Apr 19 '16 at 21:53