PHP PDO for NOT IN query in MYSQL
mysql, pdo, php, sql
Solution
It doesn't work in this way because an `IN()` clause expects a collection of values, not a comma separated string, which is what you are providing by attempting to bind them all as a single argument.
In order to make this work you will need to bind each element in the collection individually:
// Split the IDs into an array
$ids = preg_split('/\s*,\s*/', $_POST['excludeids'], -1, PREG_SPLIT_NO_EMPTY);
// Create an array of ? characters the same length as the number of IDs and join
// it together with commas, so it can be used in the query string
$placeHolders = implode(', ', array_fill(0, count($ids), '?'));
// Prepare the statement
$STH = $DBH->prepare("SELECT * FROM books WHERE id NOT IN ($placeHolders)");
// Iterate the IDs and bind them
// Remember ? placeholders are 1-indexed!
foreach ($ids as $index => $value) {
$STH->bindValue($index + 1, $value, PDO::PARAM_INT);
}
// This should now work
$STH->execute();
Problem
I have a post variable called `$_POST['excludeids']` with the following value: ``` 1,2,3,4,5,6,7,8,9 ``` I want to pass this into an SQL query through `NOT IN` so I use the following query: ``` $STH = $DBH->prepare("SELECT * FROM books WHERE id NOT IN (:excludeids)"); $STH->bindValue(':excludeids', $_POST['excludeids']); $STH->execute(); ``` Binding the variable doesn't work in this context I don't know why. What's wrong with the above query?