I'm trying to carry out the following delete statements

DELETE FROM images AS i WHERE i.image_id = 803 AND i.plant_num = 2277 LIMIT 1
DELETE FROM images AS i WHERE i.image_id = 804 AND i.plant_num = 2277 LIMIT 1
DELETE FROM images AS i WHERE i.image_id = 805 AND i.plant_num = 2277 LIMIT 1

but i am getting the error:

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 'AS i WHERE i.image_id = 803 AND i.plant_num = 2277 LIMIT 1; DELETE FROM images A' at line 1

any idea what is causing this because everything looks okay to me.

Thanks in advance

Write statements without aliase e.g.

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

thanks, i tried doin that but still get the same basic error.

Query:

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:

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 1

and here's a PHP snippet:

foreach($removals as $id)
	$sql .= "\nDELETE FROM images WHERE image_id = ".$id." AND plant_num = ".$_REQUEST['pid']." LIMIT 1;";

What does your PHP query statement print out as? I'm betting it's not what you think it is.

Also, a

describe images

would help us help you.

well the query i showed you is what prints out.

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;

and describe images is:

Field 	       Type 	                   Null 	Key 	Default 	Extra
image_id          int(10) unsigned 	   NO 	PRI 	NULL 	auto_increment
plant_num        int(10) unsigned 	   NO 	MUL 	NULL 	 
image_src 	       varchar(255) 	   NO 	  	NULL 	 
image_alt 	       varchar(64) 	   NO 	  	NULL 	 
location_taken  varchar(64) 	   YES 	  	Barbados 	 
is_vouchered    tinyint(1) 	   NO 	  	0 	 
is_best_fit 	       tinyint(1) 	   NO 	  	0

What does

SELECT * FROM images WHERE image_id = 803 AND plant_num = 2277\G

show you? If you can't find that image, then obviously you can't delete it.

nope i get back a result

btw i'm connecting as a user with delete priviliges so it isnt that either

i thought it might be the "\n" in the sql string perhaps but i took it out and still got the error

thanks for the assistance so far, i hope someone can help cause i reallly need to try and get this finished up soon

Can you execute

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

from the command line?

Can you execute that literal string from your script?

Your script might not have the same permissions you do from the command line. Do other of your scripts execute OK when accessing the database?

1064 seems to refer to using a reserved word, but I see none in your query.

Hi thanks for all the previous help but i've figured it out.

This is the php/sql query i use now:

php:

$sql .= "\nDELETE FROM images WHERE plant_num = ".$_REQUEST['pid']." AND image_id IN (".implode(",",$removals).");";

query

DELETE FROM images WHERE plant_num = 2277 AND image_id IN (803,804,805);

I'm not sure why, but MySQL didnt seem to like when i put DELETE statements one after the other like that

Huh. Yes that is very strange. Glad you got it figured out though. And thanks for posting the solution in case someone else runs into this.

You were trying to run multiple sql statements with a single mysql_query() call which is not supported. If you need to run multiple queries in a single call, then consider the use of mysqli_multi_query().

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.