Infinite classification is an old topic. Let’s take a look at how to implement it with PHP combined with Mysql.
The first method
This method is very common and traditional. Let’s look at the table structure first
Table:category
id int primary key, auto-increment
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 for every recursion. The usual approach is to first retrieve all categories 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'=>'Computer','pid'=>0),
array('id'=>2,'name'=>'mobile phone','pid'=>0),
array('id'=>3,'name'=>'Notebook','pid'=>1),
array('id'=>4,'name'=>'Desktop','pid'=>1),
array('id'=>5,'name'=>'Smartphone','pid'=>2),
array('id'=>6,'name'=>'Function machine','pid'=>2),
array('id'=>7,'name'=>'Ultrabook','pid'=>3),
array('id'=>8,'name'=>'Game Note','pid'=>3),
);
The goal is to convert it into the following structure
Computer
Notebook
Ultrabook
Game notebook
Desktop
Mobile phone
Smartphone
Function phone
If represented by an array, you can add a children key to store its subcategories:
The code is as follows:
array(
//1 corresponds to the id, which is convenient for direct reading
1 => array(
'id'=>1,
'name'=>'Computer',
'pid'=>0,
children=>array(
&array(
'id'=>3,
'name'=>'Notebook',
'pid'=>1,
'children'=>array(
//Omit here
)
),
&array(
'id'=>4,
'name'=>'Desktop',
'pid'=>1,
'children'=>array(
//Omit here
)
),
)
),
//Other categories are omitted
)
Processing process:
The code is as follows:
$tree = array();
//The first step is to use the classification id as the array key and create the children unit
foreach($categories as $category){
$tree[$category['id']] = $category;
$tree[$category['id']]['children'] = array();
}
//The second part uses references to add each category to the children array of the parent class, so that a tree structure can be formed in one traversal.
foreach ($tree as $k=>$item) {
if ($item['pid'] != 0) {
$tree[$item['pid']]['children'][] = &$tree[$k];
}
}
print_r($tree);
The printing results are as follows:
The code is as follows:
Array
(
[1] => Array
(
[id] => 1
[name] => Computer
[pid] => 0
[children] => Array
(
[0] => Array
(
[id] => 3
[name] => Notebook
[pid] => 1
[children] => Array
(
[0] => Array
(
[id] => 7
[name] => Ultrabook
[pid] => 3
[children] => Array
(
)
)
[1] => Array
(
[id] => 8
[name] => Game Notebook
[pid] => 3
[children] => Array
(
)
)
)
)
[1] => Array
(
[id] => 4
[name] => Desktop
[pid] => 1
[children] => Array
(
)
)
)
)
[2] => Array
(
[id] => 2
[name] => Mobile phone
[pid] => 0
[children] => Array
(
[0] => Array
(
[id] => 5
[name] => Smartphone
[pid] => 2
[children] => Array
(
)
)
[1] => Array
(
[id] => 6
[name] => Function phone
[pid] => 2
[children] => Array
(
)
)
)
)
[3] => Array
(
[id] => 3
[name] => Notebook
[pid] => 1
[children] => Array
(
[0] => Array
(
[id] => 7
[name] => Ultrabook
[pid] => 3
[children] => Array
(
)
)
[1] => Array
(
[id] => 8
[name] => Game Notebook
[pid] => 3
[children] => Array
(
)
)
)
)
[4] => Array
(
[id] => 4
[name] => Desktop
[pid] => 1
[children] => Array
(
)
)
[5] => Array
(
[id] => 5
[name] => Smartphone
[pid] => 2
[children] => Array
(
)
)
[6] => Array
(
[id] => 6
[name] => Function phone
[pid] => 2
[children] => Array
(
)
)
[7] => Array
(
[id] => 7
[name] => Ultrabook
[pid] => 3
[children] => Array
(
)
)
[8] => Array
(
[id] => 8
[name] => Game Notebook
[pid] => 3
[children] => Array
(
)
)
)
Advantages: The relationship is clear and it is easy to modify the relationship between superiors and subordinates.
Disadvantages: Using PHP for 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 category name
pid int parent class id, default 0
path varchar path
Sample data:
id name pid path
1 computer 0 0
2 mobile 0 0
3 notebook 1 0-1
4 Ultrabook 3 0-1-3
5 Game Book 3 0-1-3
The path field records the path from the root category to the upper-level parent category, 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 | Game Book | 0-1-3 |
+----+-----------+-------+
This method has also been adopted by many people. I summarized it as follows:
Advantages: easy query, high efficiency, 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 you all like it.
