PDO Queries with "WHERE... IN"

In an effort to enhance database access with PDO, numerous developers encounter challenges, particularly with "WHERE... IN" queries. Let's delve into the intricacies and discover the proper approach for using a list of items within a PDO prepared statement.

The "WHERE... IN" Conundrum

Consider a scenario where you need to delete items from a database based on a list of checked items from a form. Each item has a corresponding ID, which would typically be stored in an array. The conventional "WHERE... IN" query would look something like this:

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

However, this approach often results in only the first ID being deleted. This is because PDO interprets the comma-separated list as a single value, hence ignoring the subsequent IDs.

Embracing Placeholders and Parameters

To circumvent this issue, one must leverage placeholders and bind parameters. The approach involves replacing the entire comma-separated list with individual placeholders (question marks) and looping through the array to bind each ID to a placeholder. Here's how you would achieve this:

$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)");

foreach ($idlist as $id) {
  $stmt->bindParam(1, $id);
}

This revised approach ensures that each ID is treated as a separate parameter, thereby enabling accurate execution of the query.