PHP: Tree-structured algorithm 1

Reproduced from the village of Joy, has read this article before, the story is still more clear.

Product classification, multi-level tree structure of the Forum, mailing list and many other places we will encounter the problem: how to store the multi-level structure of data?

In PHP application, the data store is usually a relational database, it can save a lot of data, provide efficient data retrieval and update Services. However, the basic form of relational data is a criss-cross table, which is a planar structure, so it is necessary to make a reasonable translation if we want to store multilevel tree structure in relational database. Next, I will look at what I saw and what I have learned and some practical experience to discuss with you.

There are basically two common design methods for storing hierarchical data in a flat database:

Adjacent directory mode (adjacency list model)
Pre-sorted traversal tree algorithm (modified preorder traversal algorithm)
I am not a computer professional, also did not learn what data structure of things, so these two names are my own literal meaning turned, if wrong also please advise.

These two things sound like scary, but they're very easy to understand. Here I use a simple food catalogue as our sample data. Our data structure is like this:


Food
|
|---Fruit
| |
| |---Red
| | |
| | | |--cherry
| |
| | |---YELLOW
| |
| |--banana
|
|---meat
|
|--beef
|
|--pork
In order to take care of those English messed up PHP enthusiasts

Food: Food
Fruit: Fruit
Red: Reds
Cherry: Cherry
Yellow: Yellow
Banana: Banana
Meat: Meat
Beef: Beef
Pork: Pork

Adjacent directory mode (adjacency list model)

We often use this pattern, and many tutorials and books are also introduced. We represent the parent node of this node by adding an attribute parent to each node to describe the entire tree structure through a planar table. Based on this principle, the data in the example can be translated into the following table:

+-----------------------+
| Parent | name |
+-----------------------+
| | Food |
| Food | Fruit |
| Fruit | Green |
| Green | Pear |
| Fruit | Red |
| Red | Cherry |
| Fruit | Yellow |
| Yellow | Banana |
| Food | Meat |
| Meat | Beef |
| Meat | Pork |
+-----------------------+
We see that Pear is a child of green, and green is a child node of fruit. The root node ' Food ' has no parent node. To simply describe the problem, this example uses only name to represent a record. In the actual database, you need to use the ID of the number to mark each node, the database table structure should probably be like this: ID, parent_id, name, description. With such a table we can save the entire multilevel tree structure from the database.

Show Multilevel Tree
If we need to show such a multilevel structure requires a recursive function.

<?php
$parent is the parent of the children we want to
$level is increased when we go to the tree, deeper
Used to display a nice indented tree

function Display_children ($parent, $level)
{
Gets all the child nodes $parent a parent node
$result = mysql_query (' SELECT name from tree '.
' WHERE parent= '. $parent. '; ';

Show each child node
while ($row = Mysql_fetch_array ($result))
{
Indent Display node name
Echo str_repeat (', $level). $row [' name ']. " n ";

Call this function again to show child nodes of child nodes

Display_children ($row [' name '], $level + 1);
}
}
?>
Use this function on the root node of the entire structure (Food) to print out the entire multilevel tree structure, because the Food is the root node its parent node is empty, so call: Display_children (', 0). The contents of the entire tree are displayed:


Food
Fruit
Red
Cherry
Yellow
Banana
Meat
Beef
Pork
If you only want to show a part of the whole structure, such as the fruit part, you can call it this way:

Display_children (' Fruit ', 0);

In almost the same way we can know the path from the root node to any node. For example, the Cherry path is "Food > Fruit > Red". In order to get such a path we need to start with the deepest level "Cherry", the query gets its parent node "Red" to add it to the path, then we query Red's parent node and add it to the path, and so on until the top level of "Food"

<?php
$node is the deepest node.
function Get_path ($node)
{
Querying the parent node of this node
$result = mysql_query (' SELECT parent from tree '.
' WHERE name= '. $node. '; ';
$row = Mysql_fetch_array ($result);

Save a path with an array
$path = Array ();

Continue query up if not root node
(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 should 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 get an array like this:


Array
(
[0] => Food
[1] => Fruit
[2] => Red
)
Then how to print it into the format you want, is your business.
Disadvantage: This method is simple, easy to understand, good to start. But there are some drawbacks. Mainly because the operation speed is very slow, because each node needs to do database query, data volume when a lot of query to complete a tree. In addition, because of the recursive operation, each level of recursion needs to occupy some memory, so the efficiency is relatively low in space utilization.

Now let's take a look at another method that does not use recursive computing, which is the modified preorder tree traversal algorithm, which is less likely to be touched by the first time, and is not as easy to understand as the above method. , but because this method does not use the recursive query algorithm, has the higher query efficiency.

We first put the multilevel data on paper in the following way, write 1 on the left side of the root node food and then follow the tree down and write 2 down on the left side of the Fruit and then move on to the left and right digits along the edge of the entire tree to each node. The last number is 18 on the right side of the food. In this picture below you can see the whole number of multi-level structure marked. (Not read?) Point your finger at the number from 1 to 18 to see what's going on. Don't get it, count it again, and pay attention to moving your fingers.
These numbers indicate the relationships between the nodes, and the "Red" number is 3 and 6, which is the descendant node of "Food" 1-18. Again, we can see that all nodes with a left value greater than 2 and a right value of less than 11 are descendants of the "Fruit" 2-11.