Better method to delete multiple rows in a myql database with PHP?

Let's say I have an array with a bunch of ids for a mysql table:

$idList = array('1','2','3','4','5');

I want to delete the rows associated with each id. Which method is more preferable/better/faster (IYO)?

$idListString = implode(",",$idList);
mysql_query("DELETE FROM this_table WHERE id IN ($idListString)");

or

foreach($idList as $value) {
mysql_query("DELETE FROM this_table WHERE id = '$value'");
}

Answers


I'm no expert, but I believe

$idListString = implode(",",$idList);
mysql_query("DELETE FROM this_table WHERE id IN ($idListString)");

is faster. The reason is, it only makes one query. Less data is sent to the server and it's all processed in one go, in one command.

In general, with the other method, if you have say 300 values, that means you're making 300 additional function calls, 300 communications to the server, etc. though in practice that may vary.

edit: Further, you should always use proper MySQL escaping, even if you can be sure the data is not malicious. See http://php.net/manual/en/function.mysql-real-escape-string.php and consider using mysqli or PDO.


I think you should the first one

$idListString = implode(",",$idList);
mysql_query("DELETE FROM this_table WHERE id IN ($idListString)");

Because it cost less traffic than the second one. In the second solution, it called mysql server 5 times. It means

1.Request <--> Response
2.Request <--> Response
3.Request <--> Response
4.Request <--> Response
5.Request <--> Response
...

In my opinion If you have a lot of ids in forloop it may causes bottle neck.

But if you use the 1st, it just has one Request <---> Response.


The first method is faster, since it deletes multiple rows in single statement.


Need Your Help

Place centered text between navigation buttons from Twitter Bootstrap

html css twitter-bootstrap

Twitter Bootstrap has aligned navigation buttons that are placed on both sides of the page.

Text Transformations & Finding Entity Plural (Collection)

entity-framework entity-framework-4 t4 css

If I have a .tt template, I can use entity.Name to write out the name of an entity, e.g:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.