PDO with "WHERE... IN" queries

Question

I'm reworking some PHP code to use PDO for the database access, but I'm running into a problem with a "WHERE... IN" query.

I'm trying to delete some things from a database, based on which items on a form are checked. The length and content of the list will vary, but for this example, imagine that it's this:

$idlist = '260,201,221,216,217,169,210,212,213';

Then the query looks like this:

$query = "DELETE from `foo` WHERE `id` IN (:idlist)";
$st = $db->prepare($query);
$st->execute(array(':idlist' => $idlist));

When I do this, only the first ID is deleted. (I assume it throws out the comma and everything after it.)

I've also tried making $idlist an array, but then it doesn't delete anything.

What's the proper way to use a list of items in a PDO prepared statement?

Solution

edorian's answer worked for me, but I thought I'd elaborate a bit.

First, each of the check box names indicates that it's part of an array:

<input type="checkbox" name="foos[]" value="bar">

So on submission, I get a variable called $_POST['foos'], which is an array of the values of all the checked boxes.

Then I can do this:

//As many question marks as array entries; the last one needs no comma
$questionmarks = str_repeat("?,", count($_POST['foos'])-1) . "?";    
$query = "DELETE from `employee_customeraccount` WHERE `id` IN ($questionmarks)";
$st = $db->prepare($query);
//Each question mark is filled in with the next item in the array
$st->execute($_POST['foos']);

It works!

Answer 1

I would make $idlist and array, then simply loop through the array using foreach to delete the specific item.

$idlist = array('260','201','221','216','217','169','210','212','213');

$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` = ?");
$stmt->bindParam(1, $id);

foreach ($idlist as $item){
    $id = $item;
    $stmt->execute();
}

Answer 2

Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.

$idlist = array('260','201','221','216','217','169','210','212','213');

$questionmarks = str_repeat("?,", count($idlist)-1) . "?";

$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` IN ($questionmarks)");

and loop to bind the parameters.