MySQL delete in table problem

Question

Because I have so many categories, I created a single "categories" database table :

"id_category , parent_id , category_name , category_tag"

example :

  ----------------------------------------------------------
  | id_category | parent_id | category_name | category_tag |
  ----------------------------------------------------------
  | 1           | 0         | Cars          | cars         |
  | 2           | 1         | Parts         | parts        |
  | 3           | 2         | Accesories    | accesories   |
  ----------------------------------------------------------

the tree :

  Cars
     |_Parts
           |_Accesories

My problem occurs when I want to delete all records in the tree. How do I delete all records in the tree branch from above example.

Is that possible in mySQL with PHP?

Jake N · Accepted Answer · 2011-01-05 12:08:43Z

2

Look up InnodB, that is type of database engine your site is probably using MyISAM at the moment, also look up Cascade Deletes.

InnobDb lets you assign relationships between indicies in your table, you can then assign an action as to what happens when a row is deleted or updated.

You want to set the ON DELETE action to CASCADE, this will cascade the delete operation to child rows and their child rows. So deleting a parent will automatically delete all children, grand children, etc.

answered Jan 5, 2011 at 12:08

Jake N

10.6k12 gold badges69 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

morowind Over a year ago

I just read mysql doc...Seems is working.Thank you very much for indications.

Mikko Wilkman · Accepted Answer · 2011-01-05 12:35:19Z

1

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

What you're looking for is a foreign key on parent_id that references id_category with constraint ON DELETE CASCADE. Then you can just delete the root element (category_id 0) and you should end up with an empty table.

answered Jan 5, 2011 at 12:35

Mikko Wilkman

1,55510 silver badges8 bronze badges

Comments

user466764 · Accepted Answer · 2011-01-05 12:14:40Z

0

A database table is not essentially a tree what you would normally do is

DELETE FROM categories WHERE id_category=3

or

DELETE FROM categories WHERE id_category=3 AND parent_id=2

answered Jan 5, 2011 at 12:14

user466764

1,1567 silver badges7 bronze badges

1 Comment

Jake N Over a year ago

This isn't scalable at all. Avoid.

Nabab · Accepted Answer · 2011-01-05 12:42:07Z

0

If you don't have key with constraint, you can delete everything just by a TRUNCATE command on the table.

If you do have a key configured for cascade deletion, deleting the top item (parent_id=0) will delete all the depending and subdepending rows. In this case, deleting the first row would empty the table.

answered Jan 5, 2011 at 12:42

Nabab

2,6641 gold badge20 silver badges34 bronze badges

Collectives™ on Stack Overflow

MySQL delete in table problem

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related