0

All I can find are answers with Joins and Inner Joins,

$sql= "DELETE FROM `Item` WHERE `ItemID`='$_POST[id]';
       DELETE FROM `Info` WHERE `Item_ItemID`='$_POST[id]';
   DELETE FROM `ListPics` WHERE `Item_ItemID`='$_POST[id]';";

This gives me an error.

Error: 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 `Info` WHERE 
`Item_ItemID`='1'; DELETE FROM `ListPics` WHERE `I' at line 2

I don't really understand how joins work, and I'm not even sure they apply I really just want to delete the 3 rows from the 3 tables with one statment.

Improve this question
3
  • 1
    You can't do that with mysql_query; you can with mysqli_multi_query, which lets you send through a semi-colon separated list of commands to perform. Alternatively, you can do this with database configurations, and set up Info to delete entries when the parent $itemID is deleted. Commented Dec 19, 2013 at 20:25
  • 1
    Maybe try a transaction. Commented Dec 19, 2013 at 20:27
  • Why does this have to be one statement? Commented Dec 19, 2013 at 20:27

5 Answers 5

Reset to default
1

You can run multiple queries using mysqli_multi_query; you can pass them in as a semi-colon separated list:

$sql= "DELETE FROM `ListPics` WHERE `Item_ItemID`='$_POST[id]';
       DELETE FROM `Info` WHERE `Item_ItemID`='$_POST[id]';
       DELETE FROM `Item` WHERE `ItemID`='$_POST[id]';";

mysqli_multi_query($dbConn, $sql)

I've swapped around the order of the deletes. Because the ListPics andInfohave a foreign key in them refering toItem`, you need to delete those first, otherwise you'll get an error about violating the foreign key constraints.

Note that you can also do this with ON DELETE CASCADE in the database - using that, when you delete something from Item, anything that references ItemID as a foreign key is also removed for you. Thanks to Leonardo for the informative link.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You cannot delete from multiple tables at once, via PHP. PHP can only run one statement at a time, and you cannot do deletes with a join.

Improve this answer

6 Comments

So I need to create a loop?
coughmysqli_multi_querycough :D
I stand corrected - I had not seen that. Hopefully OP is using mysqli :)
@andrewsi I tried mysqli_multi_query but its spitting out foriegn key constainte errors
@DcHerrera - you need to reverse the order of the deletes, so the child tables are deleted first. You can't delete from item until info and listPics have no related rows.
|
1

You have to design you Item table, so that when you delete a record, it'll delete all of it's associations.

And then, and only then, you can use:

$sql= "DELETE FROM `Item` WHERE `ItemID`='$_POST[id]';

It all depends how your tables are designed, but you cand find some info here

Improve this answer

3 Comments

SO an on delete event?
That's what I was trying to explain in my comment - could you explain a little more how to do it, because I can't for the life of me remember what to do!
@andrewsi it all depends of how are the tables designed, but here is some info
0

You have to use the right delimiter to separate the commands. IN this case it appears that you are using ';' . Please make sure that that is the expected statement delimiter. You can change the delimiter too in a session. And as always try your command set in a command-line session to make sure it is syntactically correct before baking it in the program.

Improve this answer

Comments

0

You should use ON DELETE CASCADE

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.