MySQL delete in table problem

Because I have so many categories, I created a si开发者_Python百科ngle "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?

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.

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.

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

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.