PHP MySQL Remove Parent and Child Rows

Question

I have 1 MySQL table. It looks like this:

+---------+-------------+--------+ | item_id | parent_id | Name | +---------+-------------+--------+ | 1 | 0 | Home | +---------+-------------+--------+ | 2 | 1 | Sub | +---------+-------------+--------+ | 3 | 2 | SubSub | +---------+-------------+--------+ 

If I DELETE item_id 1, I want to delete the remaining parts, but how to do it?

I tried a foreign key, but it only works if you have 2 tables ??

I hope someone can help me in MySQL, maybe PHP?

Answer 1

You can, of course, use your own foreign key links with MySQL (you do not need multiple tables). However, for any foreign key support you need to use the InnoDB engine . And I assume that you are using the MyISAM engine .

With InnoDB you can create a table similar to what you already have, including a foreign key for self-binding, for example:

 CREATE TABLE `yourTable` ( `item_id` int(10) unsigned NOT NULL auto_increment, `parent_id` int(10) unsigned default NULL, `Name` varchar(50) NOT NULL, PRIMARY KEY (`item_id`), KEY `FK_parent_id` (`parent_id`), CONSTRAINT `FK_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `yourTable` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Then, when you issue a DELETE , for example:

 DELETE FROM `yourTable` WHERE `item_id` = 1; 

... it will remove every "child" row that has parent_id of 1 . If any of these "child" lines have their own children, they will also be deleted, etc. (Which means ON DELETE CASCADE ).

Answer 2

simpler than thoughts:

 DELETE FROM table WHERE id = # OR parent_id = #; //where # is the same in both places. 

Example:

 DELETE FROM table WHERE id = 1 OR parent_id = 1;