pdo prepared statements do not work, but an ordinary query does. What am I missing?

Question

For some reason I cannot get this to work:

/**
 *  $_COOKIE['MyShoppingList'] is a serialized array with intergers. 
 */
if($_COOKIE['MyShoppingList']){
    foreach(unserialize($_COOKIE['MyShoppingList']) as $recipe){
        $recipes_ids .= $recipe.',';
    }

    $sql_WHERE_NOT_IN = 'WHERE r.id NOT IN (:ids)';
}


$qry_recipes = $dbh->prepare('
    SELECT r.id drink_id, r.name drink_name
    FROM recipes r
    '.$sql_WHERE_NOT_IN.'
');
if($_COOKIE['MyShoppingList']){
    $qry_recipes->execute(array(':ids'=>rtrim($recipes_ids,',')));  //  I've verified that this is in fact a string with all the intergers sepparated with a comma.
} else {
    $qry_recipes->execute();
}

This does work like a charm:

if($_COOKIE['MyShoppingList']){
    /*  the $recipes_id is the same as before  */
    $sql_WHERE_NOT_IN = 'WHERE r.id NOT IN ('.rtrim($recipes_ids,',').')';
}


$qry_recipes = $dbh->query('
    SELECT r.id drink_id, r.name drink_name
    FROM recipes r
    '.$sql_WHERE_NOT_IN.'
');

The only difference is that the former is using prepared statements, and the latter is a pure query.

What happens is that it looks like the former, prepared, is not detecting the $recipes_ids-string..

Is there something about the $recipes_ids I'm overlooking?

rtrim(...) string (13) "12,1,2,3,9,10"  //  it's like this in both scenarios

I've tried bindParam() as well, but that resulted in this error message:

"Strict Standards: Only variables should be passed by reference"

I'm not sure what that means, but it might be telling me what I should be doing..
So please let me know..

Also; I've tried putting rtrim($recipes_ids,',') into a variable before sending it to the prepared query - but with no luck..

A prepared statement placeholder can only hold a single value, not a list. You need one placeholder per value — GordonM, Jan 28 '14 at 09:12
possible duplicate of [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) — david strachan, Jan 28 '14 at 12:17

score 2 · Accepted Answer · answered Jan 28 '14 at 09:18

2

You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

Try this way:

/**
 *  $_COOKIE['MyShoppingList'] is a serialized array with intergers. 
 */
$recipes_ids = array();
if($_COOKIE['MyShoppingList']){
    foreach(unserialize($_COOKIE['MyShoppingList']) as $recipe){
        $recipes_ids[] = $recipe;
    }

    $sql_WHERE_NOT_IN = 'WHERE r.id NOT IN (' . str_repeat('?, ', count($recipe_ids) - 1) . '?)';
}


$qry_recipes = $dbh->prepare('
    SELECT r.id drink_id, r.name drink_name
    FROM recipes r
    '.$sql_WHERE_NOT_IN.'
 ');
if($_COOKIE['MyShoppingList']){
    $qry_recipes->execute($recipe_ids);
} else {
    $qry_recipes->execute();
}

answered Jan 28 '14 at 09:18

Rafael Kassner

1,123
6
21

1

Thanks. That helped a lot. Although counting `$recipes_ids` resulted in a WARNING message telling that the second arguement in `str_repeat` could not be 0, I figured that it must be because `$recipes_ids` is not an array, but just a string with the numbers. So I tried putting `unserialize($_COOKIE['MyShoppingList'])` into the counder instead, and that looks like it worked. Trying it out now on other queries... – ThomasK Jan 28 '14 at 13:25
May I ask how I can changt it from "?"-marks to named placeholders? The rest of the query is placeholders, and I can't get it to work by just changing them into "?"-marks. – ThomasK Jan 30 '14 at 16:47
`$stmt = $pdo->prepare("SELECT * FROM table WHERE field = :param");` and `$stmt->execute(array('param' => 'value'));` – Rafael Kassner Jan 30 '14 at 18:28

pdo prepared statements do not work, but an ordinary query does. What am I missing?

1 Answers1