delete a record from two tables in php/mysql

delete a record from two tables in php/mysql

I'm trying to delete a "boat" from boat table and associated qualifications in another table using the following code:

DELETE FROM tbl_boat, tbl_qualifications   WHERE tbl_boat.BT_ID = '$bt_id' AND tbl_boat.BT_ID = tbl_qualifications.BT_ID;  

The problem is I'm receiving following 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 'WHERE tbl_boat.BT_ID = 113 AND tbl_boat.BT_ID = tbl_' at line 2 .

Appreciate any help on this.

Answer by ljhljh235 for delete a record from two tables in php/mysql

You may want to try INNER JOIN.

DELETE FROM tbl_boat  INNER JOIN tbl_qualifications  WHERE tbl_boat.BT_ID = '$bt_id' AND tbl_boat.BT_ID = tbl_qualifications.BT_ID;  

I haven't tested this, but I think this will work.

Answer by Sean Bright for delete a record from two tables in php/mysql

You have to tell MySQL how the two tables are related:

DELETE tbl_boat, tbl_qualifications  FROM      tbl_boat      INNER JOIN tbl_qualifications USING (BT_ID)  WHERE      tbl_boat.BT_ID = '$bt_id'  

Answer by Stephan for delete a record from two tables in php/mysql

You need to perform two delete :

-- DELETE all associations first ...
DELETE FROM tbl_qualifications WHERE BT_ID = '$bt_id';

-- ... then delete the boat
DELETE FROM tbl_boat WHERE BT_ID = '$bt_id';

Answer by lesto for delete a record from two tables in php/mysql

it should be

DELETE tbl_boat, tbl_qualifications FROM tbl_boat, tbl_qualifications   WHERE tbl_boat.BT_ID = '$bt_id' AND tbl_boat.BT_ID = tbl_qualifications.BT_ID;  

btw take a look at How to delete from multiple tables in MySQL?

Answer by hjpotter92 for delete a record from two tables in php/mysql

This will definitely work:

DELETE a.*, b.*  FROM tbl_boat AS a  INNER JOIN tbl_qualifications AS b      ON b.BT_ID = a.BT_ID  WHERE tbl_boat.BT_ID = '$bt_id';  

Also, if the BT_ID is INTEGER type, then remove the quotation from around $bt_id.

Answer by Shailesh Singh for delete a record from two tables in php/mysql

Try This

DELETE uploadfeat,postfeeds,postcomment FROM uploadfeat INNER JOIN postfeeds INNER JOIN postcomment  WHERE uploadfeat.id=postfeeds.postID AND uploadfeat.id=postcomment.postID AND uploadfeat.id=23  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72