Delete multiple rows dynamically with PDO?


Let's say I have a user table ( id | nome ) and that where they are listed (client-side ) I can select multiple of them and delete them at the same time. >

How could I do this on a MySQL database using PDO?

I know that MySQL allows the use of the IN clause that would serve for this type of query. Ex:

DELETE FROM usuarios WHERE id IN (37,45,58) # deletaria os ids 37, 45 e 58

The problem is that since ids would be dynamic and the PDO uses prepared-statements , I do not know how I would proceed with the code in it. I imagine it would look something like:

$query = $pdo->prepare("DELETE FROM usuarios WHERE id IN :ids");
$query->bindParam(':ids', [37,45,58]); #array de ids vindo de um $_POST, por exemplo.

Would there be any way to do something like the above code with dynamic values?

asked by anonymous 20.06.2015 / 22:43

2 answers


You can make use of str_repeat() PHP function to generate a string by repeating ? for every ID present in your array:

$ids = //... seja lá como você recupera os ids

$mParams = str_repeat('?,', count($ids) - 1) . '?';
$sth = $db->prepare("DELETE FROM usuarios WHERE id IN ($mParams)");

For example if you had a

$ids = [11, 23, 34, 47];

The str_repeat will generate an output:




$sth = $db->prepare("DELETE FROM usuarios WHERE id IN (?,?,?,?)");
20.06.2015 / 22:51

In my PDO queries I always use named parameters so it is not possible to use the "wildcard" ? , I use the following code snippet

$ids = array(); // sua variável
$sql = 'SELECT ...';
$parameters = array(); // variável de parâmetros passada para execute()
$in = array();
foreach ($ids as $key => $value) {
  $keyname = ':id' . $key;
  $parameters[$keyname] = $value;
  array_push($in, $keyname);
$sql .= 'WHERE id IN (' . join(', ', $in) . ')';
23.06.2015 / 13:14