PHP + Mysql unlimited classification methods

This article mainly introduces two methods to achieve unlimited classification of PHP + Mysql, and compares and analyzes the advantages and disadvantages of the two methods, if you need it, you can refer to the infinite classification, which is an old topic. let's take a look at how PHP works with Mysql.

Method 1

This method is a very common and traditional one. first look at the table structure.

Table: category
Id int primary key, auto-increment
Name varchar category name
Pid int parent class id. the default value is 0.
Pid of top-level classification is 0 by default. When we want to retrieve the subcategory tree of a specific category, the basic idea is recursion. of course, we do not recommend that you query the database every recursion due to efficiency issues, the common practice is to extract all categories and save them to the PHP array for processing. Finally, the results can be cached to improve the efficiency of the next request.

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

The code is as follows:

$ Categories = array (
Array ('id' => 1, 'name' => 'PC', 'pid '=> 0 ),
Array ('id' => 2, 'name' => 'cell 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', 'pid '=> 2 ),
Array ('id' => 7, 'name' => 'superscript', 'pid '=> 3 ),
Array ('id' => 8, 'name' => 'gamely', 'pid' => 3 ),
);

The goal is to convert it to the following structure:

Computer
Notebook
Superscript
Game Book
Desktop
Mobile phone
Smart machines
Function machine
If it is 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 to facilitate direct reading
1 => array (
'Id' => 1,
'Name' => 'computer ',
'Pid '=> 0,
Children => array (
& Array (
'Id' => 3,
'Name' => 'Notebook ',
'Pid '=> 1,
'Children '=> array (
// Omitted here
)
),
& Array (
'Id' => 4,
'Name' => 'desktops ',
'Pid '=> 1,
'Children '=> array (
// Omitted here
)
),
)
),
// Other categories are omitted
)

Processing process:

The code is as follows:

$ Tree = array ();
// Step 1: use the Category 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 is to add each category to the children array of the parent class by reference, so that a tree structure can be formed after a traversal.
Foreach ($ tree as $ k => $ item ){
If ($ item ['pid']! = 0 ){
$ Tree [$ item ['pid'] ['Children '] [] = & $ tree [$ k];
}
}
Print_r ($ tree );

The output is 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] => superscript
[Pid] => 3
[Children] => Array
(
)
)
[1] => Array
(
[Id] => 8
[Name] => game book
[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] => smart machine
[Pid] => 2
[Children] => Array
(
)
)
[1] => Array
(
[Id] => 6
[Name] => function machine
[Pid] => 2
[Children] => Array
(
)
)
)
)
[3] => Array
(
[Id] => 3
[Name] => notebook
[Pid] => 1
[Children] => Array
(
[0] => Array
(
[Id] => 7
[Name] => superscript
[Pid] => 3
[Children] => Array
(
)
)
[1] => Array
(
[Id] => 8
[Name] => game book
[Pid] => 3
[Children] => Array
(
)
)
)
)
[4] => Array
(
[Id] => 4
[Name] => Desktop
[Pid] => 1
[Children] => Array
(
)
)
[5] => Array
(
[Id] => 5
[Name] => smart machine
[Pid] => 2
[Children] => Array
(
)
)
[6] => Array
(
[Id] => 6
[Name] => function machine
[Pid] => 2
[Children] => Array
(
)
)
[7] => Array
(
[Id] => 7
[Name] => superscript
[Pid] => 3
[Children] => Array
(
)
)
[8] => Array
(
[Id] => 8
[Name] => game book
[Pid] => 3
[Children] => Array
(
)
)
)

Advantage: The relationship is clear, and it is easy to modify the upper-lower-level relationship.

Disadvantage: PHP is used for processing. if the number of classes is large, the efficiency will be reduced.

Method 2

This method adds a path field to the table field:

Table: category
Id int primary key, auto-increment
Name varchar category name
Pid int parent class id. the default value is 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 Super 3 0-1-3
5 Game Book 3 0-1-3
The path field records the path from the root category to the parent class at the upper level, which is indicated by id +.

In this way, assume that we want to query all descendant categories under the computer, only one SQL statement is required:

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 | Super version | 0-1-3 |
| 5 | game book | 0-1-3 |
+ ---- + ----------- + ------- +
This method has also been adopted by many people. I have summarized the following:

Advantages: Easy to query and high efficiency. you can add an index to the path field.

Disadvantage: it is difficult to update the node relationship. you need to update the path field of all later generations.

The above is all the content of this article. Which of the two methods do you like? I hope you will like it.