Multi mysql delete function using loops

Question

I have a function where i delete multiple rows in a database using foreach loops.

Here is the code below.

$ids = array(1, 3, 7, 8, 9, 32);

foreach ($ids as $id) {
    $sql = mysql->query("DELETE FROM tablename WHERE id = '$id' " );
}

This works in deleting all the rows that had the ids in the array, but my question is, how effective can this be especially if the ids are over 2000, can such looping handle over thousands of ids in an array without affecting the database? Or is there a better way of handling this?

Don't do that. `DELETE FROM tablename WHERE id IN ( 1, 3, 7, 8, 9, 32 )` is much more efficient. — Markus Zeller, Jul 28 '20 at 13:33
`$in = join(',', $ids);` and then `"DELETE FROM tablename WHERE id IN ( $in )"` — Markus Zeller, Jul 28 '20 at 13:35
You should also parameterize. You can `prepare` outside then only `execute` inside, although the whole process should be changed to one `delete`. If you were `update`ing to different values the `prepare` outside and `execute` in loop makes more sense. — user3783243, Jul 28 '20 at 13:38
The array came from a checkbox so is a programatic approach because you don't know which ids the user will select — chris, Jul 28 '20 at 13:45
@Markus Zeller i think the later approach is best since the array are dynamic, so i do $in = join(',', $ids); but i want know if this can handle over 10,000 ids without issues to the mysql — chris, Jul 28 '20 at 13:49
Or should i limit the select to 2000 maximum is that the delete comes in batches — chris, Jul 28 '20 at 13:52
Yes, you should chunk them and delete in multiple bulk steps. — Markus Zeller, Jul 28 '20 at 13:57
If this is not coming as written then this is not secure. Parameterize the query and use prepared statements. See https://stackoverflow.com/questions/37713592/explanation-of-pdo-in-clause-using-prepared-statements — user3783243, Jul 28 '20 at 14:52

Markus Zeller · Answer 1 · 2020-07-28T16:36:31.143

0

Deleting with single statements is very slow, but when, do it with prepared statements.

$query = "DELETE FROM tablename WHERE id = ?";
$sth = $pdo->prepare($query);
foreach($id in $ids) $sth->execute([$id]);

Better is using bulk statements with the IN keyword.

$ids = array(1, 3, 7, 8, 9, 32);
$in = join(',', $ids);
$query = "DELETE FROM tablename WHERE id IN ( $in )";

Update

Better use prepared statements here, too, when not sure, that the IDs are all legal

$ids = array(1, 3, 7, 8, 9, 32);
$marks = substr(str_repeat('?,',  count($ids)), 0, -1);
$query = "DELETE FROM tablename WHERE id IN ( $marks )";
$sth = $dbo->prepare($query);
$sth->execute($ids);

When you have thousands of data to delete, then better chunk them.

If you want to delete all entries of a table, then truncating is the best choice.

TRUNCATE tablename

edited Jul 28 '20 at 16:36

answered Jul 28 '20 at 14:24

Markus Zeller

8,516
2
29
35

Both approaches should use prepared statements. – user3783243 Jul 28 '20 at 14:51
First approach is. For second I do not see any advantage in speed as long IDs are numbers. – Markus Zeller Jul 28 '20 at 16:24
Second is injectible. See OP's update in comments. `$ids = array()` somes from a client side input. – user3783243 Jul 28 '20 at 16:28
1

I appreciate everyone but my question was not about sql injection, neither was i posting as a guru to educate other readers, the aim of stackflow is not to show coding prowess but that any developer can get help as per what he needs. I know sql injection and prepared statement but my problem was bulk delete which am satisfied with the answers i got here especially from @Markus Zeller please when a question is asked lets focus on what the developer wants to archieve instead of pulling him into confusion – chris Jul 28 '20 at 16:31
@Markus Zeller but you posted as comment not as answer, please how do i mark comments as correct? Guide me and i will do that right away – chris Jul 28 '20 at 16:44
The one i saw was a flag and that's like a flag off, but wat ever the case is just know am grateful – chris Jul 28 '20 at 16:49

Multi mysql delete function using loops

1 Answers1

Update