Paul M. Jones

UPDATE (2016-05-30): Rasmus Schultz comments that "this does work – it was fixed after this article was published." So apparently the issue described herein has been fixed. I'll leave the article in place for archival purposes.

Prior to PHP 5.2.1, you could do this with PDO ...

<?php
// assume $pdo is PDO connection
$sql = "SELECT * FROM some_table
        WHERE col1 = :foo
        OR col2 = :foo
        OR col3 = :foo";

$sth = $pdo->prepare($sql);

$sth->bindValue('foo', 'bar');

$sth->execute();
?>

... and PDO would happily bind the value 'bar' to every ':foo' placeholder in the statement.

Sadly, this is no longer the case in PHP 5.2.1. For valid reasons of security and stability in memory handling, as noted to me by Wez Furlong, the above behavior is no longer supported. That is, you cannot bind a single parameter or value to multiple identical placeholders in a statement. If you try it, PDO will throw an exception or raise an error, and will not execute the query. In short, you now need to match exactly the number of bound parameters or values with the number of placeholders.

In most cases, I'm sure that's not a problem. However, in Solar, we can build queries piecemeal, so we can't necessarily know in advance how many placeholders there are going to be in the final query.

Also, it's often convenient to throw an array of data against a statement with placeholders, and only bind to the placeholders that have elements in the data array. Alas, this too is no longer allowed in PDO under PHP 5.2.1, because the number of bound values might not match the number of placeholders.

As a result, the newest Solar_Sql_Adapter::query() method includes some code to examine the statement and try to extract the named placeholders that PDO expects to see. Given the above example statement, PDO will expect placeholders for :foo, :foo2, and :foo3 (PDO auto-numbers repeated placeholder names). While a bit brain-dead, it does seem to do its job tolerably well ... at least well enough to get around this newly-implemented (but apparently always-planned) behavior.

The code in the query() method looks something like this; note that we call it by sending along an array of $data to bind as values into the statement.

// prepare the SQL command and get a statement handle
$sth = $this->_pdo->prepare($sql);

// find all :placeholder matches.  note that this will
// find placeholders in literal text, which will cause
// errors later.  so in general, you should *either*
// bind at query time *or* bind as you go, not both.
preg_match_all(
    "/W:([a-zA-Z_][a-zA-Z0-9_]+?)W/m",
    $sql . "n",
    $matches
);

// bind values to placeholders, adding numbers as needed
// in the way that PDO renames repeated placeholders.
$repeat = array();
foreach ($matches[1] as $key) {

    // only attempt to bind if the data key exists.
    // this allows for nulls and empty strings.
    if (! array_key_exists($key, $data)) {
        // skip it
        continue;
    }

    // what does PDO expect as the placeholder name?
    if (empty($repeat[$key])) {
        // first time is ":foo"
        $repeat[$key] = 1;
        $name = $key;
    } else {
        // repeated times of ":foo" are treated by PDO as
        // ":foo2", ":foo3", etc.
        $repeat[$key] ++;
        $name = $key . $repeat[$key];
    }

    // bind the $data value to the placeholder name
    $sth->bindValue($name, $data[$key]);
}

// now we can execute, even if we had multiple identical
// placeholders in the statement.
$sth->execute();

With this code in place, we can now bind one 'foo' value to many identical ':foo' placeholders.

NB: Do not try doing this with bound parameters, or you are likely to run into memory problems.

UPDATE (2016-05-30): Rasmus Schultz comments that "this does work – it was fixed after this article was published." So apparently the issue described herein has been fixed. I'll leave the article in place for archival purposes.