PHP PDO for NOT IN query in MYSQL

Question

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 to an SQL query via 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(); 

Variable binding does not work in this context. I do not know why. What is wrong with the above request?

Answer 1

This does not work this way because the IN() clause expects a set of values, not a comma-separated string, which is what you provide, trying to bind them all as one argument.

To do this, you need to link each item 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(); 

Answer 2

You will need to go through the identifiers (first blow them up into an array) and dynamically create new parameters, both in the SQL string and using bindValue .

Answer 3

  $all_id = array(1, 2, 3, 4,5); $countArr = count($all_id); for($countArr; $countArr > 0; $countArr--) $in[]= '?'; $in = implode(', ', $in); $stmt = $dbh->prepare(" SELECT ID FROM b_iblock_element WHERE XML_ID NOT IN ( ".$in.") "); if ($stmt->execute($all_id)) { while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo '<pre>'; print_r($row); echo'</pre>'; } } $stmt->execute();