php mysql 结构_使用PHP MySQL的类别层次结构（按顺序）

I am trying to ORDER my all categories and sub-categories in a hierarchy:

The main point is how to get them from MySQL ORDERLY (using POSITION field)

Cat A --> position 10

Sub-Cat 1 --> position 10

Sub_Sub_Cat 1 --> position 20

Sub_Sub_Cat 2 --> position 10

Sub_Cat 2 --> position 30

Cat B --> position 20

Cat C --> position 30

MySQL code:

CREATE TABLE IF NOT EXISTS `categories` (

`category_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`position` smallint(5) unsigned,

`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0'

PRIMARY KEY (`category_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

解决方案

You want to traverse the tree using SQL? That is not possible with the adjacency list model, you have to use the nested sets model. Then you can just ORDER BY left to get the whole tree in the correct order.