PHP一层一层的判断,php-MySQL规范化,具有第一层子类别和第二层...

使用parent_id指定记录所属的类别：

+--------+---------------+-----------+

| cat_id | name | parent_id |

+--------+---------------+-----------+

| 1 | Electronics | 0 |

| 2 | Appliances | 0 |

| 3 | Cell phones | 1 |

| 4 | Computers | 1 |

| 5 | Tablets | 1 |

| 6 | Smartphones | 3 |

| 7 | Tablet Phones | 3 |

+--------+---------------+-----------+

要选择所需的数据,您可以对以下查询进行改进：

mysql> SELECT

-> a.name AS main_category,

-> b.name AS second_level_category,

-> c.name AS thrid_level_category

-> FROM categories AS a

-> LEFT JOIN categories AS b ON (a.cat_id=b.parent_id)

-> LEFT JOIN categories AS c ON (b.cat_id=c.parent_id)

-> WHERE a.parent_id=0;

+---------------+-----------------------+----------------------+

| main_category | second_level_category | thrid_level_category |

+---------------+-----------------------+----------------------+

| Electronics | Cell phones | Smartphones |

| Electronics | Cell phones | Tablet Phones |

| Electronics | Computers | NULL |

| Electronics | Tablets | NULL |

| Appliances | NULL | NULL |

+---------------+-----------------------+----------------------+

@最后评论：

mysql> desc categories;

+-----------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+----------------+

| cat_id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(50) | YES | | NULL | |

| parent_id | varchar(50) | YES | | NULL | |

+-----------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

mysql> show create table categories\G

*************************** 1. row ***************************

Table: categories

Create Table: CREATE TABLE `categories` (

`cat_id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

`parent_id` varchar(50) DEFAULT NULL,

PRIMARY KEY (`cat_id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)