Deleting from multiple tables

Anon

I have two tables. One table is called links. This table stores the url, name, description, and category of a href link. My other table is the categories table that stores all the categories that you can choose for the different links that you have in the links table. When I go to delete a category out of the categories table I want it to also delete all the links in the links table that have the same category as the category that I'm deleting from categories table. Right now I can delete a category from the categories table using this command "$sql = "DELETE FROM categories WHERE id=$id";" $id is the id of the record of the category I'm deleting. I want make it so it deletes all the links in the link table that have the same category as the one I'm deleting from the categories table.

Any help on this would be greatly appreciated!!!!!!

calevans

What database are you using? If it's MySQL then you will have to issue 2 delete statements. one for links and the other for categories.

If you are using a RDBMS that allows for triggers you can setup a cascading delete trigger will delete all the children records in links when you delete a category from categories.

The other thing you can do, in PHP, is to create an object called categories that has a method called delete. Then in your method put all the code to delete form both tables. You are still issuing 2 delete statements but you are grouping them in one piece of code. (IMHO, this is a better way anyhow. I'm not a big fan of triggers. If you are not careful, they can introduce performance problems in your system.

IMHO,
Cal

Cal Evans
- Senior Internet Dreamer
- Techno-Mage
- Last of the great Internet Burma-Shave poets.
http://www.calevans.com
*

punkdba

Brent,

That's dependent on your RDBMS.  In oracle, it's quite simple:  specify the CASCADE option.  Alternately, you could manually eliminate the child records -- i.e., "delete from LINKS where id=_category_id;"  
You should try to let the RDBMS enforce your constraints, etc -- setup the keys with an "ON DELETE CASCADE" option.  Good luck.

Anon

Thanks, I'm using mySQL. I didn't know you would have to use two delete statements. So it's called triggers when you delete with just one statement? That's good to know. Maybe mySQL will support that some day! 🙂

Anon

I'm using mySQL. Does mySQL support what your talking about?