How to Effectively Delete Records with \'WHERE... IN\' Clauses in PDO Queries?

PDO Queries with "WHERE... IN" Clauses

When working with PHP's PDO for database access, constructing "WHERE... IN" queries can be challenging. In this case, the goal is to delete records from a table based on a list of IDs obtained from a form.

Original Query

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

Problem

This query only deletes the first ID in the list, discarding the remaining IDs due to the comma separator.

Solution Using Question Marks

Since prepared statements cannot mix values with control flow logic (the commas), it is necessary to use one placeholder per value in the list. This is achieved by creating a comma-separated string of question marks and binding the individual IDs to them.

<code class="php">$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($id);
}</code>

This approach requires looping through the list to bind each ID sequentially, ensuring that all records are deleted as intended.

The above is the detailed content of How to Effectively Delete Records with \'WHERE... IN\' Clauses in PDO Queries?. For more information, please follow other related articles on the PHP Chinese website!