Delete multiple rows dynamically with PDO?

Question

Delete multiple rows dynamically with PDO?

Navigation

#1 by (9 votes)
#2 by (1 votes)

12

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.
$query->execute();

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

php mysql pdo

asked by anonymous 20.06.2015 / 22:43

2 answers

1

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

Routing and Url friendly with asp.net mvc How to edit a list in C #?

score 9 · Accepted Answer

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)");
$sth->execute($ids);

For example if you had a

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

The str_repeat will generate an output:

?,?,?,?

Getting

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