MySQL/PHP mass delete using array: Best method?

Question

I need to do a mass delete from a MySQL table via PHP (using PDO). The table has a field called 'id' and all the id's to be deleted are stored in an array. There may be 5 rows to delete from the table or there may be 5000+.

My immediate thought is to use a foreach and iterate through the array deleting one at time. However something tells me that may not be a very good method if there are a lot of elements in the array.

Any thoughts on a best method for doing this type of delete?

@mcryan is right. But, honestly, iterating over 5,000 array elements containing SQL record ids for a SQL Delete command isn't really detrimental to performance, unless you plan to integrate the process into a public user interface on a high-traffic domain. Basically, it might take 10 seconds for you. Which isn't a bad thing. It's bad when a user waits 10 seconds. You can also iterate over your array and build a query string. In some cases, using a where clause with OR is better for performance than using IN. If performance matters, test. — axiom82
– axiom82, Commented Jun 13, 2014 at 14:50

War10ck · Accepted Answer · 2014-06-13 15:01:43Z

3

Use the following query replacing table_name with the name of the table. $listofIdsToDelete is the the comma separated string obtained by exploding the array of ids'

$query = "DELETE FROM `table_name` WHERE id IN ($listofIdsToDelete)";

EDIT:

You will get a better performance if you prepare:

$pQuery = "DELETE FROM table_name WHERE id = ?"

and bind each id item in the loop and execute .

Thanks to UselessIntern for the correction mentioned below and the suggestion to include this comment in the answer

edited Jun 13, 2014 at 15:01

War10ck

12.5k7 gold badges46 silver badges56 bronze badges

answered Jun 13, 2014 at 14:53

nrip

1114 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Useless Intern Over a year ago

It's an array use implode(',' , $listofIdsToDelete) instead.

nrip Over a year ago

You will get a better performance if you prepare $pQuery = "DELETE FROM table_name WHERE id = ?" and bind each item in the array

Useless Intern Over a year ago

Ah so add that tidbit of information to your answer. It's not clear, to me at least, at a glance.

Chris Cummings Over a year ago

Thanks! Didn't know about IN and I wondered if there was prepared statement way of doing it...will look into that.

Steve · Accepted Answer · 2014-06-13 14:54:59Z

2

use WHERE IN:

$ids = array(1,2,3,5,6,7);
$db->exec('DELETE from table WHERE id IN ?', [implode(',' $ids)]);

answered Jun 13, 2014 at 14:54

Steve

20.5k5 gold badges47 silver badges71 bronze badges

Collectives™ on Stack Overflow

MySQL/PHP mass delete using array: Best method?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related