php中的子类别

Question

I am doing a project in PHP, MySQL. I need to create a database in which there is a category to which there can be many subcategories ( like for a course book I can have physics, computer science, mathematics etc.).

Also there can be some categories which do not have subcategories (say Mythological Books).

The administrator has to add new category or subcategory via a form in PHP page which then updates the database.

How should I design the structure of my table where if admin for following two cases to be incorporated:

1. Admin only wants a subcategory to be added then upon selecting its parent category, it should be done.

2. Admin wants to add a new parent category and there exists no subcategory for it.

Please suggest how should I create my table(s).

Answer 1

Create a table called 'categories' with columns 'id', 'name', 'parent'.

Then use it like this:

1, Hardware, null
2, Storage, 1
3, Video, 1
4, Harddisks, 2
...

Here is a simple example with books:

Categories table:

+----+--------------------+--------+
| id | name               | parent |
+----+--------------------+--------+
|  1 | Comics             |   NULL |
|  2 | Programming        |   NULL |
|  3 | SQL/PHP            |      2 |
|  4 | Java               |      2 |
|  5 | Marvel             |      1 |
|  6 | Mythological Books |   NULL |
+----+--------------------+--------+

Books table:

+----+---------------------------------------+----------+
| id | name                                  | category |
+----+---------------------------------------+----------+
|  1 | PHP/MYSQL for dummies                 |        3 |
|  2 | Iron Man and the prisoners of azkaban |        5 |
+----+---------------------------------------+----------+

If you want to show all the books in the Programming>SQL/PHP category you just simply get them with a select query.

mysql> select * from books where category = 3;
+----+-----------------------+----------+
| id | name                  | category |
+----+-----------------------+----------+
|  1 | PHP/MYSQL for dummies |        3 |
+----+-----------------------+----------+
1 row in set (0,00 sec)