从表中删除嵌套类别

Question

I have a table called categories which looks like this

+-------------+--------------+---------------+
|  id         |  catName     |  parentId     |
+-------------+--------------+---------------+
|  1          | Category 1   |  0            |
|  2          | Category 2   |  0            |
|  3          | Sub Cat 1    |  1            |
|  4          | Sub Cat 2    |  1            |
|  5          | Sub sub cat 1|  4            |
|  6          | Sub sub cat 2|  4            |
|  7          | Category 2   |  0            |
+-------------+--------------+---------------+

It would be easy to delete a category and its direct child:

mysql_query("DELETE FROM `categories` WHERE `id`='$id'");
mysql_query("DELETE FROM `categories` WHERE `parentId`='$id'");

However I need to be able to delete ALL children of a category. For example if Category 1 was deleted Sub Cat 1, Sub Cat 2, Sub sub cat 1, Sub sub cat 2 will be deleted

Any help appreciated.

Answer 1

You could use foreign keys, a very nice feature of mysql. This is a kind of integrity check for referencing different relations. If you create a foreign key, mysql ensures that referenced entries exist, for example. During the creation of a foreign key, you can define what mysql should do if the "parent element" is deleted. You can specify "SET NULL", "UPDATE" or "DELETE CASCADE". This means, if you delete a category, every connected sub category is deleted as well. And because of the fact that every sub category is the parent category of a sub sub category, those are deleted as well.