Dynamically delete multiple lines with PDO?

Question

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

How could I do this in a Mysql database using PDO?

I know that Mysql allows the use of the clause IN which would serve 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 like the ids would be dynamic and PDO uses Prepared-statements, I don’t know how I would do with the code on it. I imagine it would be 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.
$query->execute();

Would have some way to do something like the code above, with dynamic values?

Answer 1

You can use the function str_repeat() PHP to generate a string repeating the ? for each ID present in your matrix:

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

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

For example if I had one

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

The str_repeat will generate an output:

?,?,?,?

Staying

$sth = $db->prepare("DELETE FROM usuarios WHERE id IN (?,?,?,?)");

Answer 2

in my PDO queries I always use named parameters then it is not possible to use the "joker" ?, 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) . ')';