PDO use parameter twice?

PHP
1

If I run this:


$values = array('foo' => 'foo', 'bar' => 'bar');
$sth = $pdo->prepare('SELECT :foo, :bar, :foo');
$sth->execute($values);
print_r($sth->fetchAll());

the second :foo is 0:


Array
(
    [0] => Array
        (
            [?] => 0
            [0] => foo
            [1] => bar
            [2] => 0
        )

)

(I also don’t know why there’s a ? index, but there always seems to be.)

Is there any way to get PDO to actually recognise the variables multiple times?

Specifically I want to do:


INSERT INTO table foo = :foo, bar = :bar ON DUPLICATE KEY UPDATE foo = :foo, bar = :bar

It works for inserts, but all the updates fill the record with zeros.

2 

$values = array(':foo' => 'foo', ':bar' => 'bar');
3

Prefixing values with : makes 0 difference. Output is still:



Array
(
    [0] => Array
        (
            [?] => 0
            [0] => foo
            [1] => bar
            [2] => 0
        )

)
4

http://us3.php.net/manual/en/pdo.prepare.php

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement.

5

well that just sucks, what’s the point in using a named parameter then?

6

It has to do with the fact the PDO has to convert named parameters to the “?” question marks for MySQL. MySQL doesn’t understand named parameters.

7

Just wait until you want to pass an array to a WHERE id IN ( :ids) statement. Can’t be done cleanly.

I think the named parameters stuff is more of a SQL Prepared statement limit as opposed to a php restriction. Might be wrong.

It’s actually fairly easy to do a bit of processing on your sql statement and implement the functionality you want. I always wrap the PDO object just so I can tweak this stuff.

8

Yes that’s what I’ve resorted to doing… why can’t PDO do this internally though? Makes no sense that it doesn’t.