tundoopani tundoopani - 7 months ago 40
SQL Question

Better method to delete multiple rows in a MySQL 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'");
}

Answer

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.