Implementation Algorithm for parsing infinite left/right values

I. Introduction
In terms of product classification, multi-level tree structure forums, mail lists, and many other places, we all encounter the following problem: how to store multi-level structured data? In PHP applications, relational databases are usually used to store background data. They can store a large amount of data and provide efficient data retrieval and Update Services. However, the basic form of relational data is an interactive table, which is a flat structure. If you want to store a multi-level tree structure in a relational database, you need to perform reasonable translation work. Next, I will discuss what I have seen and some practical experiences with you:
There are basically two common design methods for storing hierarchical data in a flat database:
* Adjacency list model)
* Modified preorder tree traversal algorithm)
I am not a computer professional and have never learned anything about data structures. Therefore, these two names are literally translated by myself. If they are wrong, please give me more advice. These two things seem very scary and easy to understand.

Ii. Model
Here I use a simple food directory as our sample data.
The data structure is as follows:

Copy codeThe Code is as follows: Food

| --- Fruit

| --- Red

| -- Cherry

| + --- Yellow

| + -- Banana

+ --- Meat
| -- Beef
+ -- Pork

To take care of PHP fans who are confused about EnglishCopy codeThe Code is as follows: Food
Fruit: Fruit
Red: Red
Cherry: Cherry
Yellow: Yellow
Banana: bananas
Meat: Meat
Beef: Beef
Pork: Pork

Iii. Implementation
1. adjacency list model)
This mode is often used and has been introduced in many tutorials and books. We add a property parent to each node to represent the parent node of the node and describe the entire tree structure through a flat table. According to this principle, the data in the example can be converted into the following table:
The following code is used:Copy codeThe Code is as follows: + --------------------- +
| Parent | name |
+ ----------------------- +
| Food |
| Food | Fruit |
| Fruit | Green |
| Green | Pear |
| Fruit | Red |
| Red | Cherry |
| Fruit | Yellow |
| Yellow | Banana |
| Food | Meat |
| Meat | Beef |
| Meat | Pork |
+ ----------------------- +

We can see that Pear is a child node of Green and Green is a child node of Fruit. The root node 'food' has no parent node. To briefly describe this problem, the name is used in this example to represent a record. In the actual database, you need to use the numerical id to mark each node. The table structure of the database should be like this: id, parent_id, name, descr partition ption.
With such a table, we can store the entire multi-level tree structure through the database.
Multi-level tree display. If we need to display such a multi-level structure, we need a recursive function.
The following code is used:Copy codeThe Code is as follows: <? Php
// $ Parent is the parent of the children we want to see
// $ Level is increased when we go deeper into the tree,
// Used to display a nice indented tree
Function display_children ($ parent, $ level ){
// Obtain all child nodes of a parent node $ parent
$ Result = mysql_query ("
SELECT name
FROM tree
WHERE parent = '". $ parent ."'
;"
);
// Display each subnode
While ($ row = mysql_fetch_array ($ result )){
// Display the node name in indentation
Echo str_repeat ('', $ level). $ row ['name']." \ n ";
// Call this function again to display the subnode of the subnode
Display_children ($ row ['name'], $ level + 1 );
}
}
?>

Use this function for the root node (Food) of the entire structure to print the entire multi-level tree structure. Because Food is the root node, its parent node is empty, so we call it like this: display_children ('', 0 ). The contents of the entire tree are displayed:Copy codeThe Code is as follows: Food
Fruit
Red
Cherry
Yellow
Banana
Meat
Beef
Pork

If you only want to display a part of the entire structure, such as the Fruit part, you can call display_children ('fruit', 0 );
Using the same method, we can know the path from the root node to any node. For example, the path of Cherry is "Food>; Fruit>; Red ". To get such a path, we need to start from the deepest level "Cherry" and query it to get its parent node "Red" and add it to the path, then we query the Red parent node and add it to the path, and so on until the "Food" at the highest level. The following code is used:Copy codeThe Code is as follows: <? Php
// $ Node is the deepest node
Function get_path ($ node ){
// Query the parent node of this node
$ Result = mysql_query ("
SELECT parent
FROM tree
WHERE name = '". $ node ."'
;"
);
$ Row = mysql_fetch_array ($ result );
// Save the path with an Array
$ Path = array ();
// If it is not the root node, continue to query up
// (The root node does not have a parent node)
If ($ row ['parent']! = ''){
// The last part of the path to $ node, is the name
// Of the parent of $ node
$ Path [] = $ row ['parent'];
// We shoshould add the path to the parent of this node
// To the path
$ Path = array_merge (get_path ($ row ['parent']), $ path );
}
// Return the path
Return $ path;
}
?>;

If you use this function for "Cherry": print_r (get_path ('cherry'), you will get an array like this:Copy codeThe Code is as follows: Array (
[0] => Food
[1] => Fruit
[2] => Red
)

How to print it into the desired format is yours.
Disadvantages:
This method is simple, easy to understand, and easy to use. But there are also some shortcomings. It is mainly because the running speed is very slow. Because each node needs to query the database, when the data volume is large, many queries are required to complete a tree. In addition, due to recursive operations, each level of recursion consumes some memory, so the space utilization efficiency is relatively low.

2. Pre-sorted traversal Tree Algorithm
Now let's take a look at another method that does not use Recursive Computing and is faster. This is the pre-sorted traversal tree (modified preorder tree traversal algorithm)
This method is rarely used and is not easy to understand for the first time. However, because this method does not use recursive query algorithms, it has higher query efficiency.

First, we will draw the multilevel data on the paper in the following way, write 1 on the left side of the root node Food, and then write 2 down the tree to the left side of the Fruit, and then proceed, along the edge of the entire tree, each node is marked with numbers on the left and right. The last digit is 18 on the right of the Food. In the figure below, you can see the multilevel structure of the entire labeled number. (Not understood? Point your finger at the number from 1 to 18 to see what's going on. I still don't understand. I'll try again. Pay attention to moving your finger ).
These numbers indicate the relationship between each node. "Red" numbers are 3 and 6, and it is the child node of "Food" 1-18. Similarly, we can see that all nodes with a left value greater than 2 and a right value less than 11 are child nodes of "Fruit" 2-11.
The following code is used:Copy codeThe Code is as follows: 1 Food 18

+ ------------------------------ +

2 Fruit 11 12 Meat 17

+ ------------- ++ ------------ +

3 Red 6 7 Yellow 10 13 Beef 14 15 Pork 16

4 Cherry 5 8 Banana 9

In this way, the entire tree structure can be stored in the database through left and right values. Before proceeding, let's take a look at the data tables that have been organized below.
The following code is used:Copy codeThe Code is as follows: + ---------- + ----- +
| Parent | name | lft | rgt |
+ ---------- + ------------ + ----- +
| Food | 1 | 18 |
| Food | Fruit | 2 | 11 |
| Fruit | Red | 3 | 6 |
| Red | Cherry | 4 | 5 |
| Fruit | Yellow | 7 | 10 |
| Yellow | Banana | 8 | 9 |
| Food | Meat | 12 | 17 |
| Meat | Beef | 13 | 14 |
| Meat | Pork | 15 | 16 |
+ ---------- + ------------ + ----- +

Note:Since "left" and "right" have special meanings in SQL, we need to use "lft" and "rgt" to represent left and right fields. In addition, the "parent" field is no longer required to represent the tree structure. That is to say, the following table structure is enough.
The following code is used:Copy codeThe Code is as follows: + ------------ + ----- +
| Name | lft | rgt |
+ ------------ + ----- +
| Food | 1 | 18 |
| Fruit | 2 | 11 |
| Red | 3 | 6 |
| Cherry | 4 | 5 |
| Yellow | 7 | 10 |
| Banana | 8 | 9 |
| Meat | 12 | 17 |
| Beef | 13 | 14 |
| Pork | 15 | 16 |
+ ------------ + ----- +

Now we can get the data from the database. For example, we need to get all the nodes under "Fruit" to write the query statement as follows:Copy codeThe Code is as follows: SELECT * FROM tree WHERE lft BETWEEN 2 AND 11;

The query returns the following results.
The following code is used:Copy codeThe Code is as follows: + ------------ + ----- +
| Name | lft | rgt |
+ ------------ + ----- +
| Fruit | 2 | 11 |
| Red | 3 | 6 |
| Cherry | 4 | 5 |
| Yellow | 7 | 10 |
| Banana | 8 | 9 |
+ ------------ + ----- +

As you can see, all these nodes can be obtained through a single query. In order to display the entire tree structure as the recursive function above, we also need to sort such queries. Sort by the Left value of the node:Copy codeThe Code is as follows: SELECT * FROM tree WHERE lft BETWEEN 2 AND 11 order by lft ASC;

The remaining questions show how to indent the hierarchy.
The following code is used:Copy codeThe Code is as follows: <? Php
Function display_tree ($ root ){
// Obtain the left and right values of the root node.
$ Result = mysql_query ("
SELECT lft, rgt
FROM tree
WHERE name = '". $ root ."'
;"
);
$ Row = mysql_fetch_array ($ result );
// Prepare an empty right value Stack
$ Right = array ();
// Obtain all child nodes of the Root Node
$ Result = mysql_query ("
SELECT name, lft, rgt
FROM tree
WHERE lft between' ". $ row ['lft ']."' AND '". $ row ['rgt']."'
Order by lft ASC
;"
);
// Display each row
While ($ row = mysql_fetch_array ($ result )){
// Only check stack if there is one
If (count ($ right)> 0 ){
// Check whether the node should be removed from the stack
While ($ right [count ($ right)-1] <$ row ['rgt ']) {
Array_pop ($ right );
}
}
// Indent the node name
Echo str_repeat ('', count ($ right). $ row ['name']." \ n ";
// Add this node to the stack
$ Right [] = $ row ['rgt '];
}
}
?>

If you run the above functions, the results will be the same as those of recursive functions. But our new function may be faster, because there are only two database queries.
It is easier to know the path of a node. If we want to know the path of Cherry, we will use its left and right values 4 and 5 for a query.Copy codeThe Code is as follows: SELECT name FROM tree WHERE lft <4 AND rgt>; 5 order by lft ASC;

The following result is displayed:
The following code is used:Copy codeThe Code is as follows: + ------------ +
| Name |
+ ------------ +
| Food |
| Fruit |
| Red |
+ ------------ +

How many child nodes does a node have? Very simple, descendant Total = (right-left-1)/2Copy codeThe Code is as follows: descendants = (right-left-1)/2

Don't believe it? Calculate it by yourself.
Using this simple formula, we can quickly calculate that the "Fruit 2-11" node has four child nodes, while the "Banana 8-9" node has no child nodes, that is to say, it is not a parent node.
Amazing, right? Although I have used this method many times, it is amazing every time I do this.
This is indeed a good method, but is there any way to help us establish such a data table with left and right values? Here we will introduce a function that can automatically convert a table with the name and parent structure into a data table with the left and right values.
The following code is used:Copy codeThe Code is as follows: <? Php
Function rebuild_tree ($ parent, $ left ){
// The right value of this node is the left value + 1
$ Right = $ left + 1;
// Get all children of this node
$ Result = mysql_query ("
SELECT name
FROM tree
WHERE parent = '". $ parent ."'
;"
);
While ($ row = mysql_fetch_array ($ result )){
// Recursive execution of this function for each
// Child of this node
// $ Right is the current right value, which is
// Incremented by the rebuild_tree function
$ Right = rebuild_tree ($ row ['name'], $ right );
}
// We 've got the left value, and now that we 've processed
// The children of this node we also know the right value
Mysql_query ("
UPDATE tree
SET
Lft = '". $ left ."',
Rgt = '". $ right ."'
WHERE name = '". $ parent ."'
;"
);
// Return the right value of this node + 1
Return $ right + 1;
}
?>

Of course, this function is a recursive function. We need to run this function from the root node to reconstruct a tree with left and right values.Copy codeThe Code is as follows: rebuild_tree ('food', 1 );

This function looks a little complicated, but its function is the same as the number of a table manually. It is to convert a three-dimensional multi-layer structure into a data table with left and right values.
How can we add, update, and delete a node for such a structure?
There are two methods to add a node:
First,Retain the original name and parent structure and add data to the data using the old method. After each added data, use the rebuild_tree function to re-number the entire structure.
Second,The more efficient way is to change the values on the right of all new nodes. For example, we want to add a new fruit "Strawberry" (Strawberry) which will become the last child node of the "Red" node. First, we need to make some space for it. The right value of "Red" should be changed from 6 to 8, and the left and right values of "Yellow 7-10" should be changed to 9-12. And so on, we can know that if we want to free up space for the new value, we need to add 2 to all nodes with the left and right values greater than 5 (5 is the right value of the last "Red" subnode. So we perform database operations like this:Copy codeThe Code is as follows: UPDATE tree SET rgt = rgt + 2 WHERE rgt> 5;
UPDATE tree SET lft = lft + 2 WHERE lft> 5;

In this way, we can create a new data node in the new inserted value. Its left and right values are 6 and 7 respectively.Copy codeThe Code is as follows: insert into tree SET lft = 6, rgt = 7, name = 'strawberry ';

Make another query! How is it? Soon.

Iv. Conclusion
Now, you can use two different methods to design your multi-level database structure. It depends on your personal judgment, however, I prefer the second method for a structure with a large number of layers. If you want to query a small amount of data that requires frequent addition and updates, the first method is simpler.
In addition, if the database supports rebuild_tree () and space-free operations, you can also write the database trigger function, which is automatically executed during insertion and update, in this way, the running efficiency is better, and the SQL statement you add to the new node becomes simpler.