25

I'm reworking some PHP code to use PDO for the database access, but I'm running into a problem with a "WHERE... IN" query.

I'm trying to delete some things from a database, based on which items on a form are checked. The length and content of the list will vary, but for this example, imagine that it's this:

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

Then the query looks like this:

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

When I do this, only the first ID is deleted. (I assume it throws out the comma and everything after it.)

I've also tried making $idlist an array, but then it doesn't delete anything.

What's the proper way to use a list of items in a PDO prepared statement?

PhoneixS
  • 10,574
  • 6
  • 57
  • 73
Nathan Long
  • 122,748
  • 97
  • 336
  • 451
  • You mustn't add a solution in your question. If you think is a better answer, answer your own question or suggest the edit to the original answer. – PhoneixS Sep 05 '17 at 15:10
  • @PhoneixS Fine advice, but I last touched this question in 2010 and haven't really worked with PHP since that year. Feel free to edit if you like. – Nathan Long Sep 06 '17 at 19:03

3 Answers3

36

Since you can't mix Values (the Numbers) with control flow logic (the commas) with prepared statements you need one placeholder per Value.

$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)");

and loop to bind the parameters.

Nathan Long
  • 122,748
  • 97
  • 336
  • 451
edorian
  • 38,542
  • 15
  • 125
  • 143
  • 2
    Nice, thanks. I use it like this though: `implode(',',str_split(str_repeat('?',count($idList))));` – Alec Jul 06 '11 at 23:50
  • 8
    Or: `implode(',', array_fill(0, count($idList), '?'));` (if count > 0 which is mandatory for the answer as well), see [`array_fill`](http://php.net/array_fill), [`str_repeat`](http://php.net/str_repeat) – hakre Jan 26 '12 at 13:46
  • @NathanLong solution: `` On submission, I get a variable called `$_POST['foos']`, which is an array of the values of all the checked boxes. Then: //As many question marks as array entries; the last one needs no comma `$questionmarks = str_repeat("?,", count($_POST['foos'])-1) . "?"; $query = "DELETE from `employee_customeraccount` WHERE `id` IN ($questionmarks)"; $st = $db->prepare($query); //Each question mark is filled in with the next item in the array $st->execute($_POST['foos']);` It works! – PhoneixS Sep 07 '17 at 08:36
  • 1
    What @hakre mentioned plus ->execute(array_values($idlist )); to get numeric keys no matter where $idList came from. – cottton Apr 07 '18 at 12:02
8

This may be helpful too:

https://phpdelusions.net/pdo#in

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($arr);
$data = $stm->fetchAll();
Ivan P.
  • 832
  • 2
  • 9
  • 26
-4

I would make $idlist and array, then simply loop through the array using foreach to delete the specific item.

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

$stmt = $dbh->prepare("DELETE FROM `foo` WHERE `id` = ?");
$stmt->bindParam(1, $id);

foreach ($idlist as $item){
    $id = $item;
    $stmt->execute();
}
ssergei
  • 1,289
  • 9
  • 21