Pdo Multiple Named Placeholders Doesnt Retrieve Data

PDO multiple named placeholders doesnt retrieve data

bindParam Requires a reference

The problem is caused by the way you bind parameters in the foreach loop.

foreach($bindings as $placeholder=>$bound){
    echo $placeholder . " - " . $bound."<br/>";
    $stmt->bindParam($placeholder, $bound);
}

bindParam requires a reference. It binds the variable, not the value, to the statement. Since the variable in a foreach loop is reset at the start of each iteration, only the last reference to $bound is left intact, and you end up binding all your placeholders to it.

That's why your code works when $query['where'] contains only one entry, but fails when it contains more than one.

You can solve the problem in 2 ways:

Pass by reference

foreach($bindings as $placeholder => &$bound) {  //pass $bound as a reference (&)
    $stmt->bindParam($placeholder, $bound);     // bind the variable to the statement
}

Pass by value

Use bindValue instead of bindParam:

foreach($bindings as $placeholder => $bound) {  
    $stmt->bindValue($placeholder, $bound);     // bind the value to the statement
}

Why PDO doesn't allow multiple placeholders with the same name?

Is there any setting or a tweak to bypass this?

Yes, there is. You can turn emulation mode ON and be able to use the same placeholder multiple times.

So the described behavior is observed only when the emulation is turned OFF. I don't really understand why it is so but here is an explanation from Wez Furlong (the PDO author):

The change was made for two reasons; first and foremost, if you re-use the same variable in a bind, it is possible to induce a crash when using some drivers. It’s not possible to guarantee to do the right thing, and having a way to trigger a crash can sometimes be used as an attack vector for a security exploit.

The second reason is that of portability. Some drivers would internally perform this check and error out. If you code against the drivers that don’t enforce this, then your code won’t work on those that don’t.

http://paul-m-jones.com/archives/243#comment-740

PHP's PDO prepared statement: am I able to use one placeholder multiple times?

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.

Named placeholders in with PDO mySQL causing server error

First create/get the object/instance then use its methods; not the other way round.

// Insert user input into table
$STH = $DBH->prepare("INSERT INTO foods ( name ) VALUES ( :name )");
$STH->bindParam(':name', $name);
$STH->execute();

and (nit-picking mode: on) there's no need for the variable $name

// Insert user input into table
$STH = $DBH->prepare("INSERT INTO foods ( name ) VALUES ( :name )");
$STH->bindValue(':name', $_POST['food']);
$STH->execute();

will do, and so would

// Insert user input into table
$STH = $DBH->prepare("INSERT INTO foods ( name ) VALUES ( :name )");
$STH->execute(array('name'=>$_POST['food']));

PDO prepared statements IN clause with named placeholders doesn't work as expected

This should work for you:

So as already said in the comments you need a placeholder for each value which you want to bind into the IN clause.

Here I create first the array $ids which only holds the plain ids, e.g.

[2, 3]

Then I also created the array $preparedIds which holds the placeholders as array, which you then later use in the prepared statement. This array looks something like this:

[":id2", ":id3"]

And I also create an array called $preparedValues which holds the $preparedIds as keys and $ids as values, which you then later can use for the execute() call. The array look something like this:

[":id2" => 2, ":id3" => 3]

After this you are good to go. In the prepared statement I just implode() the $preparedIds array, so that the SQL statement look something like this:

... IN(:id2,:id3) ...

And then you can simply execute() your query. There I just array_merge() your $preparedValues array with the other placeholders array.

<?php

    $ids = array_map(function($item){
        return $item->id;
    }, $entitlementsVOs);

    $preparedIds = array_map(function($v){
        return ":id$v";
    }, $ids);

    $preparedValues = array_combine($preparedIds, $ids);

    $timestart = (!empty($_GET['start']) ? $_GET['start'] : NULL );
    $timeend = (!empty($_GET['end']) ? $_GET['end'] : NULL );

    $statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(" . implode(",", $preparedIds) . ") AND timestart >= :timestart AND timestart + timeduration <= :timeend");
    $statement->setFetchMode(\PDO::FETCH_CLASS, get_class(new EventVO()));

    if($statement->execute(array_merge($preparedValues, ["timestart" => $timestart, "timeend" => $timeend]))) {
        return $statement->fetchAll();
    } else {
        return null;
    }

?>

Also I think you want to put an if statement around your query, since your query will not run if the values of $timestart and $timeend are NULL.

PDO not binding placeholders

Remove single quotes ' :

SELECT id FROM guests WHERE email=:eml AND password =:pwd

Setting PDO/MySQL LIMIT with Named Placeholders

The problem is that execute() quotes the numbers and treats as strings:

From the manual - An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

<?php 
public function getLatestWork($numberOfSlides=10, $type=0) {

    $numberOfSlides = intval(trim($numberOfSlides));

    $STH = $this->_db->prepare("SELECT slideID
                                FROM slides
                                WHERE visible = 'true'
                                AND type = :type
                                ORDER BY order
                                LIMIT :numberOfSlides;");

    $STH->bindParam(':numberOfSlides', $numberOfSlides, PDO::PARAM_INT);
    $STH->bindParam(':type', $type, PDO::PARAM_INT);

    $STH->execute();
    $result = $STH->fetchAll(PDO::FETCH_COLUMN);

    return $result;
}
?>

PDO Parameterized Query - Reuse named placeholders?

PDO::prepare states that "you cannot use a named parameter marker of the same name twice in a prepared statement", so I guess that's a no then.

---

Related Topics