How to Properly Execute \"WHERE... IN\" Queries with Multiple IDs in PDO?

Proper PDO Syntax for "WHERE... IN" Queries with Multiple IDs

When deleting database records based on a list of IDs, maintaining data integrity is crucial. One common challenge encountered with PDO is executing "WHERE... IN" queries with multiple values.

In your case, the query fails to delete all but the first ID because it treats the comma-separated list as a single value. To resolve this, you need to include a placeholder for each ID in the list.

Solution:

To correctly use a list of IDs in a PDO prepared statement, follow these steps:

1. Convert the comma-separated ID list into an array, e.g., $idlist = ['260', '201', '221', ...];.
2. Create a string of question marks representing the number of placeholders needed, e.g., $questionmarks = str_repeat("?,", count($idlist)-1) . "?";. This will generate a string like "?,,,,,,,,," for 10 IDs.
3. Prepare the PDO statement with the question marks string, e.g., $stmt = $dbh->prepare("DELETE FROM foo WHERE id IN ($questionmarks)");.

4. Finally, loop through the ID array and bind each value to its corresponding placeholder, e.g.,

   <code class="php">foreach ($idlist as $id) {
    $stmt->bindParam(..., $id);
   }</code>

By following these steps, you can execute "WHERE... IN" queries with multiple IDs accurately and maintain data consistency.

The above is the detailed content of How to Properly Execute \"WHERE... IN\" Queries with Multiple IDs in PDO?. For more information, please follow other related articles on the PHP Chinese website!