Parsing PHP unlimited classification methods and code

This article provides a detailed analysis of PHP's unlimited classification methods and Code. For more information, see

This article provides a detailed analysis of PHP's unlimited classification methods and Code. For more information, see

Whether you want to build your own forum, publish messages on your website or write your own CMS program, you will encounter the situation of storing hierarchical data in the database. At the same time, unless you use a database like XML, the tables in the relational database are not hierarchical and they are just a flat list. Therefore, you must find a way to convert a hierarchical database.

The storage tree structure is a common problem. He has several solutions. There are two main methods: the adjacent list model and the improved preorder traversal tree algorithm.

In this article, we will explore these two methods for storing hierarchical data. I will give an example of an online grocery store tree. This Food Store organizes food by category, color, and variety. The tree structure is as follows:

This article contains code examples to demonstrate how to save and obtain data. I chose PHP to Write examples, because I often use this language, and many people also use or know this language. You can easily translate them into your own language.

The Adjacency List Model)
The first method we want to try is also the best-the method is called the "adjacent List Model" or the "recursive method ". It is a very elegant method because you only need a simple method to iterate in your tree. In our grocery store, the list of adjacent items is as follows:

As you can see, save a "parent" Node for each node. We can see that "Pear" is a child node of "Green", while the latter is a child node of "Fruit", and so on. Root Node, U.S. server, "Food", its parent node has no value. For simplicity, I only use the "title" value to identify each node. Of course, in the actual database, you need to use the digit ID.

Display tree
Now we have put the tree into the database, and we have to write a display function. This function starts from the root node-nodes without a parent node-and displays all the child nodes of this node. For these subnodes, the function also needs to obtain and display the subnodes of the subnode. Then, for their subnodes, the function will display all the subnodes, and so on.

You may have noticed that there is a common pattern for the description of such functions. We can simply write only one function to obtain the subnodes of a specific node. This function then calls itself to each subnode to display its subnodes again. This is the "recursive" mechanism, known as the Hong Kong virtual host. Therefore, this method is called "recursive method ".

To implement the entire tree, we only need to use an empty string as response $ parent and response $ level = 0: display_children ('', 0) when calling the function ); the function returns the tree chart of our grocery store as follows:
Food
Fruit
Red
Cherry
Yellow
Banana
Meat
Beef
Pork

Note: If you only want to view one subtree, you can tell the function to start from another node. For example, to display the "Fruit" subtree, you only need display_children ('fruit', 0 );
The Path to a Node
Using similar functions, we can also query the path of a node if you only know the name or ID of the node. For example, the path of "Cherry" is "Food"> "Fruit"> "Red ". To obtain this path, our function must start from the deepest level: "Cheery ". Find the parent node of the node and add it to the path. In our example, the parent node is "Red ". If we know that "Red" is the parent node of "Cherry.

This function now returns the path of the specified node. The path is returned as an array, so we can use print_r (get_path ('cherry'); to display the result:
Array
(
[0] => Food
[1] => Fruit
[2] => Red)
Insufficient
As we have seen, this is indeed a good method. It is easy to understand and the code is also very simple. But what are the disadvantages of the neighbor list model? In most programming languages, it runs very slowly and is very inefficient. This is mainly caused by recursion. Each time we query a node, We need to access the database.

Each database query takes some time, which slows down the processing of huge trees.

The second reason that this function is not too fast may be your language. Unlike Lisp, most languages are not designed for recursive functions. For each node, the function must call itself to generate a new instance. In this way, for a layer-4 tree, you may need to run four function copies at the same time. Every function occupies a piece of memory and requires some time for initialization. In this way, recursion is slow when processing big trees.

Improved pre-order traversal tree
Now let's look at another storage tree method. Recursion may be slow, so we try not to use recursive functions. We also want to minimize the number of database queries. It is best to query only once each time.

First, we should open the tree horizontally. Start from the root node ("Food") and write 1 on the left. Then write 2 to the left of "Fruit" in the tree Order (from top to bottom. In this case, you walk along the boundary of the tree (this is "traversal") and write numbers on the left and right sides of each node at the same time. Finally, we return to the root node "Food" and write 18 on the right. Below is a tree labeled with numbers, and the order of traversal is marked with arrows.

We call these numbers the left and right values (for example, the left Value of "Food" is 1 and the right value is 18 ). As you can see, these numbers reflect the relationship between each node on time. Because "Red" has 3 and 6 values, it is followed by a "Food" node with 1-18 values. Similarly, we can infer that all nodes with a left value greater than 2 and a right value less than 11 will be followed by "Food" nodes with 2-11. In this way, the tree structure is stored through the left and right values. This method for calculating nodes in the entire tree is called the "improved forward traversal Tree" algorithm.

Before proceeding, let's take a look at the values in our table:

Note that the words "left" and "right" have special meanings in SQL. Therefore, we can only use "lft" and "rgt" to represent these two columns. In fact, Mysql can be expressed with "'", for example, "'left'", which can be included in "[]" in MSSQL, for example, "[left]". in this way, it will not conflict with keywords .) We do not need the "parent" column here. We only need to use lft and rgt to store the tree structure.

Retrieve tree
If you want to display the tree with the left and right values, you must first identify the nodes you want to obtain. For example, if you want to obtain the "Fruit" subtree, You need to select the nodes with the left value between 2 and 11. Use SQL statements to express:
SELECT * FROM tree WHERE lft BETWEEN 2 AND 11;
This will return:

Okay, now the entire tree is in a query. Now we need to display this tree like the previous recursive function. We need to add an order by clause in this query. If you add or delete rows and virtual hosts from a table, your table may be in an incorrect order. Therefore, we need to sort the rows by their left values.
SELECT * FROM tree WHERE lft BETWEEN 2 AND 11 order by lft ASC;
There is only the indentation problem.

To display the tree structure, the child nodes should be slightly more indented than their parent nodes. We can save a stack with the right value. Each time you start from a subnode of a node, you add the right value of the node to the stack. You also know that the right value of the child node is smaller than the right value of the parent node. By comparing the right value of the current node and the previous node in the stack, you can determine whether you are displaying the child node of the parent node. When this node is displayed, You need to delete its right value from the stack. To obtain the number of layers of the current node, you only need to count the elements in the stack.

If you run this code, you can get the same result as the recursive function discussed in the previous section. This function may be faster: it does not use recursion and only uses two queries.

Node path
With the new algorithm, we need to find another method to obtain the path of the specified node. In this way, we need a list of the node's ancestor.

The new table structure does not require much effort. For example, for a 4-5 "Cherry" node, you will find that the Left values of the ancestor are less than 4, and the right values are greater than 5. In this way, we can use the following query:
SELECT title FROM tree WHERE lft <4 AND rgt> 5 order by lft ASC;
Note: like the preceding query, we must use an order by clause to sort nodes. This query will return:

+ ------- +
| Title |
+ ------- +
| Food |
| Fruit |
| Red |
+ ------- +

Now we only need to connect all rows to get the path of "Cherry.
How many subsequent nodes are there? How Many Descendants
If you give me the left and right values of a node, I can tell you how many subsequent nodes he has. Just use a little math knowledge.
Because each subsequent node increases the right value of this node by 2 in turn, the number of subsequent nodes can be calculated as follows:
Descendants = (right-left-1)/2
Using this simple formula, I can immediately tell you that the "Fruit" node of 2-11 has four subsequent nodes. The "Banana" node of 8-9 is only one subnode, not the parent node.

Automated tree traversal
Now you have something to do with this table. We should learn how to automatically create a table. This is a good exercise. First, we use a small tree. We also need a script to help us count nodes.
Let's write a script to convert an adjacent list into a forward traversal tree table.

This is a recursive function. You need to start from rebuild_tree ('food', 1);, this function will get the subnodes of all "Food" nodes.

If no child node exists, the left and right values of the child node are directly set. The left value has been given. 1. the right value is the left value plus 1. If a child node exists, the function repeats and returns the last right value. The right value is used as the right value of "Food.

Recursion makes this function a little complicated and difficult to understand. However, this function does get the same result. He walked along the tree and added every node he saw. After you run this function, you will find that the left and right values are the same as expected (a quick test method: the right value of the root node should be twice the number of nodes ).

Add a node
How do we add a node to this tree? There are two ways: Keep the "parent" column in the table and re-run the rebuild_tree () function-a simple but not elegant function; alternatively, you can update the left and right values of all nodes on the right.

The first idea is relatively simple. You can use the adjacent list method to update and use the improved forward traversal tree to query. If you want to add a new node, you only need to insert the node into the table and set the parent column. Then, you only need to re-run the rebuild_tree () function. This is simple, but it is not efficient for big trees.

The second method is to update all nodes on the right of the new node. Let's take a look at the example. We need to add a new fruit-"Strawberry" as the last child node of "Red. First, we need to make up a space. The right value of "Red" is changed from 6 to the "Yellow" node of-10 to 9-12, and so on. Updating the "Red" Node means adding 2 to all nodes with the left and right values greater than 5.

Let's use the following query:
UPDATE tree SET rgt = rgt + 2 WHERE rgt> 5;
UPDATE tree SET lft = lft + 2 WHERE lft> 5;
Now we can add a new node "Strawberry" to fill the new space. The left value of this node is 6 and the right value is 7.
Insert into tree SET lft = 6, rgt = 7, title = 'strawberry ';
If we run the display_tree () function, we will find that our new "Strawberry" node has been successfully inserted into the tree: Food
Fruit
Red
Cherry
Strawberry
Yellow
Banana
Meat
Beef
Pork

Disadvantages
First, it seems hard to understand how to improve the forward-order traversal algorithm. Of course, it does not have a simple method of joining the list. However, once you get used to the left and right attributes, they become clearer. You can use this technology to complete all the things that can be done in the street list, at the same time, it is faster to improve the forward-order traversal algorithm. Of course, the update tree requires a lot of queries, which takes a little longer, but only one query can be used to retrieve nodes.

Summary
Now you are familiar with the two methods in the database storage tree. Although I am here to improve the performance of the forward-order traversal tree algorithm, the method of the list can be better under special circumstances. This is left for your decision.

Last point: as I have already said, we recommend that you use the node title to reference this node. You should follow the basic rules for database standardization. I didn't use a digital logo because the example is hard to read.

Algorithm 3

In MYSQL, the data table is roughly

Create table Table_Types
(
Id integer not null AUTO_INCREMENT,
Parent_id INTEGER,
Node VARCHAR (255 ),
Primary key (id)
)

For example, the purple is the ID number of the data record. The number in the box is the node field of each record, and the parent ID and... of the record are recorded...

In this way, if we want to insert a record with a new ID of 13 under the record with ID 7, the node of the new record is 1, 2, 7, 13

To find all the subnodes under a node, you do not need to use recursion, as long as an SQL statement.

For example, "check ID 2 to record all subnodes"

Select * from Table_Types where node like "1, 2, %"

We will discuss the effectiveness and shortcomings of this algorithm!