CODEWITHSUNDEEP
phppdoprepared-statement
ThomasK
ThomasK

Reputation: 2220

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

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..

Upvotes: 0

Views: 64

Answers (1)

Rafael Kassner
Rafael Kassner

Reputation: 1124

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();
}

Upvotes: 2

Related Questions