Multiple deletes with PHP and MySQL

A question from Askville:

The code below only seems to delete one record, and does not work when trying to do an array delete.. What the heck is wrong with the code?

// ===QueryString===
// delete.php?q=169|170|171|
$arr = explode('|', $q);
for ($i = 0; $i < count($arr); $i++) {
  $query .= "DELETE FROM aCar WHERE aID = '".$arr[i]."'";
  $result =mysql_query($query);
}

Let’s start by reading the documentation:

Description

resource mysql_query ( string $query [, resource $link_identifier ] )

mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that’s associated with the specified link_identifier .

Note the “multiple queries are not supported” bit.

In addition, even if mysql_query() did support multiple queries, sending multiple queries when a single one would do just fine is a bad idea because of the per-query overhead. So you should consider something along these lines:

$arr = explode('|', $_GET['q']);
foreach ($arr as $key=>$value) {
  if (!is_numeric($value)) {
    unset($arr[$key]);
  }
}
$query = "DELETE FROM aCar WHERE aID IN ('" .
  implode("', '", $arr) . "')";
$result = mysql_query($query);

Note the use of $_GET[‘q’] instead of $q (read up on register_globals) and the foreach() loop checking members of $arr for being numeric. The latter is necessary to prevent deletion of all records by a malicious user running delete.php?q=%.