MySQL Error 1064 when DELETEing

[dipperdan]

Hi,

I'm getting an error 1064 when trying to delete some rows from a table.

SQL is:

DELETE FROM images WHERE image_id = 803 AND plant_num = 2277 LIMIT 1;
DELETE FROM images WHERE image_id = 804 AND plant_num = 2277 LIMIT 1;
DELETE FROM images WHERE image_id = 805 AND plant_num = 2277 LIMIT 1;
 

Error is:

Could not update the database:
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; DELETE FROM images WHERE image_id = 804 AND plant_num = 2277 LIMIT 1; DELETE F' at line

PHP is:
[PHP]foreach($remova ls as $id)
$sql .= "\nDELETE FROM images WHERE image_id = ".$id." AND plant_num = ".$_REQUEST['pid']." LIMIT 1;"; [/PHP]

[ronverdonk]

You are generating a multi-SQLstatement. That is not allowed in Mysql. So you will have to execute each DELETE statement after you generated it, like[php]foreach($remova ls as $id)
mysql_query("DE LETE FROM images WHERE image_id=$id AND plant_num = ".$_REQUEST['pid']." LIMIT 1");
[/php]Ronald

[Atli]

If you want to execute multiple queries in one go, the MySQL Improved Extension (mysqli) can do so via the mysqli::multi_q uery method.

[Atli]

Although...
In your case it would probably be better to use a single query, using the IN() function.
Like for example:

DELETE FROM myTable WHERE myTable.ID IN(1, 2, 4, 6, 8)