Method to realize unlimited classification in PHP+Mysql

Infinite classification is an old topic. Let’s take a look at how to implement PHP combined with Mysql.

[Related learning recommendations: php programming (video), mysql video tutorial】

First method

This method is very common and traditional. Let’s look at the table structure first.

Table: category
id int primary key, autoincrement
name varchar category name
pid int parent class id, default 0
The pid of the top category is 0 by default. When we want to take out the sub-category tree of a certain category, the basic idea is recursion. Of course, due to efficiency issues, it is not recommended to query the database every time recursively. The usual approach is to take out all categories first and save them in a PHP array. , and then process it, and finally cache the results to improve the efficiency of the next request.

First build an original array, which can be pulled directly from the database:

The code is as follows:

$categories = array(
    array('id'=>1,'name'=>'电脑','pid'=>0),
    array('id'=>2,'name'=>'手机','pid'=>0),
    array('id'=>3,'name'=>'笔记本','pid'=>1),
    array('id'=>4,'name'=>'台式机','pid'=>1),
    array('id'=>5,'name'=>'智能机','pid'=>2),
    array('id'=>6,'name'=>'功能机','pid'=>2),
    array('id'=>7,'name'=>'超级本','pid'=>3),
    array('id'=>8,'name'=>'游戏本','pid'=>3),
);

The goal is to convert it into the following Structure

Computer
Notebook
Ultrabook
Gamebook
Desktop
Mobile phone
Smartphone
Functional phone
If represented by an array, You can add a children key to store its subcategories:

The code is as follows:

array(
    //1对应id，方便直接读取
    1 => array(
        'id'=>1,
        'name'=>'电脑',
        'pid'=>0,
        children=>array(
            &array(
                'id'=>3,
                'name'=>'笔记本',
                'pid'=>1,
                'children'=>array(
                    //此处省略
                )
            ),
            &array(
                'id'=>4,
                'name'=>'台式机',
                'pid'=>1,
                'children'=>array(
                    //此处省略
                )
            ),
        )
    ),
    //其他分类省略
)

Processing process:

The code is as follows:

$tree = array();
//第一步，将分类id作为数组key,并创建children单元
foreach($categories as $category){
    $tree[$category['id']] = $category;
    $tree[$category['id']]['children'] = array();
}
//第二部，利用引用，将每个分类添加到父类children数组中，这样一次遍历即可形成树形结构。
foreach ($tree as $k=>$item) {
    if ($item['pid'] != 0) {
        $tree[$item['pid']]['children'][] = &$tree[$k];
    }
}
print_r($tree);

The printing result is as follows:

The code is as follows:

Array
(
    [1] => Array
        (
            [id] => 1
            [name] => 电脑
            [pid] => 0
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [name] => 笔记本
                            [pid] => 1
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 7
                                            [name] => 超级本
                                            [pid] => 3
                                            [children] => Array
                                                (
                                                )
                                        )
                                    [1] => Array
                                        (
                                            [id] => 8
                                            [name] => 游戏本
                                            [pid] => 3
                                            [children] => Array
                                                (
                                                )
                                        )
                                )
                        )
                    [1] => Array
                        (
                            [id] => 4
                            [name] => 台式机
                            [pid] => 1
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [2] => Array
        (
            [id] => 2
            [name] => 手机
            [pid] => 0
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 5
                            [name] => 智能机
                            [pid] => 2
                            [children] => Array
                                (
                                )
                        )
                    [1] => Array
                        (
                            [id] => 6
                            [name] => 功能机
                            [pid] => 2
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [3] => Array
        (
            [id] => 3
            [name] => 笔记本
            [pid] => 1
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 7
                            [name] => 超级本
                            [pid] => 3
                            [children] => Array
                                (
                                )
                        )
                    [1] => Array
                        (
                            [id] => 8
                            [name] => 游戏本
                            [pid] => 3
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [4] => Array
        (
            [id] => 4
            [name] => 台式机
            [pid] => 1
            [children] => Array
                (
                )
        )
    [5] => Array
        (
            [id] => 5
            [name] => 智能机
            [pid] => 2
            [children] => Array
                (
                )
        )
    [6] => Array
        (
            [id] => 6
            [name] => 功能机
            [pid] => 2
            [children] => Array
                (
                )
        )
    [7] => Array
        (
            [id] => 7
            [name] => 超级本
            [pid] => 3
            [children] => Array
                (
                )
        )
    [8] => Array
        (
            [id] => 8
            [name] => 游戏本
            [pid] => 3
            [children] => Array
                (
                )
        )
)

Advantages: The relationship is clear, and it is simple to modify the superior-subordinate relationship.

Disadvantages: Using PHP processing, if the number of categories is huge, the efficiency will also be reduced.

The second method

This method is to add a path field to the table field:

Table:category
id int primary key , auto-increment
name varchar classification name
pid int parent class id, default 0
path varchar path
Sample data:

id name pid path
1 computer 0 0
2 Mobile phone 0 0
3 Notebook 1 0-1
4 Ultrabook 3 0-1-3
5 Game notebook 3       0-1-3
path field records the classification from the root The path to the upper-level parent class is represented by id '-'.

In this way, assuming we want to query all descendant categories under the computer, we only need one sql statement:

select id,name,path from category where path like (select concat(path, '-',id,'%') as path from category where id=1);
Result:

---- ----------- ---- ---
| id | name | path |
---- ----------- -------
| 3 | notebook| 0-1 |
| 4 | Ultrabook| 0-1-3 |
| 5 | Gamebook| 0-1-3 |
---- ----------- -- -----
This method has also been adopted by many people. I summarized it as follows:

Advantages: easy query, high efficiency, and the path field can be indexed.

Disadvantages: Updating node relationships is troublesome and requires updating the path fields of all descendants.

The above is the entire content of this article. Two methods, which one do you prefer? Hope everyone likes it.

Related recommendations: Programming video course

The above is the detailed content of Method to realize unlimited classification in PHP+Mysql. For more information, please follow other related articles on the PHP Chinese website!