deleting from multiple tables

[bobby_rahman]

Hiya I just wanted to know if there was any tips people could offer me regarding cascade deletes. im using mysql and understand it dont support cascade or nested mysql queries so I wana to hard code delete query that traverses the necesssary tables using php.

For e.g. I had a Project table, which contains loads of bugs (in the
bug table). Every bug is stored with the foreign key projid so to
know which bugs are related to which projects. Now inside every bug
I allow the option to add a followup to update the bug such as
status of the bug....(This data stored in the bug_followup table).
The table bug_followup has a field bugid in it that relates to the bug table.

Now say I want to delete a project I can get all the bugs in that
project with:
select bugid from bug, project where bug.projid=project.projid;

Now I can get all the followups of the bug also doing(im guessing):
select * from bug_followup, bug where bugid=bug.bugid. But just not
sure how to join up the queries?I should do this?

Is there a quick and easy way to delete from multiple tables in php: ......Im guessing I need a loop of some sort?

<psuedocode>
For every bug in the project
check each one to see if has followups
if it does
{
delete all followups
delete bug from bug table
}
else
{
delete bug from bug table
}
delete project where projid='$projid'

Any help much appreciated

Thanx

Bobster

[jllydgnt]

Here is an idea(You might have to change your database structure, but it makes things a lot easier):

Let's say you have these two tables:

PROJECT
- project_id
- description

BUGS
- bug_id
- project_id //this is the foreign key the associates this bug with a project
- description

Now, you want to delete a project and all bugs associated with the project.

All you need to know is the project_id of the project you want to delete.

Then you run two delete queries.

DELETE FROM BUGS WHERE project_id = '$project_id';

DELETE FROM PROJECT WHERE project_id = '$project_id';

You could have an infinite number of tables that contain some kind of bug related information(like bug_followup or whatever) just include a project_id field in each to associate it with that project. Then you have a generic delete query where you just switch out the table name. You could make a function that took a table name as an argument if you didn't feel like writing the full delete query string and then the actually query function(mysql_query($sql)) everytime.

Does this answer your question?