I've searched for how to do this but haven't found anything that has made me 100% sure.
I have a main table:
messages
... and several supporting tables...
comments
ratings
alerts
favourites
The main table and it's supporting tables are connected by an id column. All records in the supporting tables have a field which holds a record id of of the record they belong to in the main table. The tables and the connected fields are like so:
messages -> message_id
comments -> comment_mid
ratings -> rating_mid
alerts -> alert_mid
favourites -> favourite_mid
So what I want is that when a user deletes a record from the "messages" table, all it's corresponding records will also be deleted from the supporting tables.
I'm not a sure of the exact syntax to use, especially selecting all the tables.
We'll call the submitted record id to be deleted "$deletethis" -Does this look correct?:
DELETE FROM messages, comments, ratings, alerts, favourites
WHERE messages.message_id = '$deletethis'
AND comments.comment_mid = '$deletethis'
AND ratings.rating_mid = '$deletethis'
AND alerts.alert_mid = '$deletethis'
AND favourites.favourite_mid = '$deletethis'
Is this the correct way to delete records from multiple tables?
Thank you very much as always!
Peter
