How to Delete Multiple Items with `WHERE... IN` in PDO Prepared Statements?

PDO Queries with "WHERE... IN" Clauses

When utilizing the "WHERE... IN" clause in PDO queries, developers often encounter issues resulting in only the deletion of the first item from the list. This arises due to the incompatibility of mixing values (numbers) with control flow logic (commas) within prepared statements. To resolve this, each value within the list requires its own placeholder.

Solution:

1. Separate the values into an array, e.g.:

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

2. Construct a string of question marks representing the placeholders for each value, with a comma separating them, e.g.:

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

3. Prepare the query with the question marks as the "IN" clause parameters, e.g.:

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

4. Iterate through the $idlist array and bind each value to a placeholder, e.g.:

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

Once the statement is prepared and the parameters bound, executing the query will result in the successful deletion of all items specified in the $idlist array.

The above is the detailed content of How to Delete Multiple Items with `WHERE... IN` in PDO Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!