Examples of PHP Techniques: algorithms for tree-shaped structures

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.

1 Food 18
|
+---------------------------------------+
| |
2 Fruit Meat 17
| |
+------------------------+ +---------------------+
| | | |
3 Red 6 7 yellow Beef Pork 16
| |
4 Cherry 5 8 Banana 9

This allows the entire tree structure to be stored in the database by the left and right values. Before we go on, let's take a look at the data tables we've sorted below.

+-----------------------+-----+-----+
| 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: Because "left" and "right" have special meanings in SQL, we need to use "LfT" and "RGT" to represent the relevant fields. In addition, the "Parent" field is no longer required in this structure to represent a tree structure. This means that the following table structure is sufficient.

+------------+-----+-----+
| 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 |
+------------+-----+-----+
Okay, now we can get the data from the database, for example, we need to get all the nodes under "Fruit" to write query statements like this: SELECT * from tree WHERE lft BETWEEN 2 and 11; This query gets the following results.

+------------+-----+-----+
| name | LfT | RGT |
+------------+-----+-----+
| Fruit | 2 | 11 |
| Red | 3 | 6 |
| Cherry | 4 | 5 |
| Yellow | 7 | 10 |
| Banana | 8 | 9 |
+------------+-----+-----+
See, as long as a query can get all these nodes. To be able to display the entire tree structure as the recursive function above, we also need to sort the query. To sort with the left value of a node:

SELECT * from tree WHERE lft BETWEEN 2 and one order by LfT ASC;
The rest of the problem shows how to indent the hierarchy.

<?php
function Display_tree ($root)
{
To get 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);

Prepares an empty right-value stack
$right = Array ();

Get all the descendants of the base point
$result = mysql_query (' SELECT name, LFT, RGT from tree '.
' WHERE lft BETWEEN '. $row [' LfT ']. ' and '.
$row [' RGT ']. ' Order by LfT ASC; ');

Show each row
while ($row = Mysql_fetch_array ($result))
{
Only check stack if there is one
if (count ($right) >0)
{
Check if we should move the node out of the stack
while ($right [count ($right) -1]< $row [' RGT '])
{
Array_pop ($right);
}
}

The name of the indented display node
Echo Str_repeat (", Count ($right)). $row [' name ']." n ";

Add this node to the stack
$right [] = $row [' RGT '];
}
}
?>
If you run the above function, you will get the same result as the recursive function. Just our new function may be faster because there are only 2 database queries. It's simpler to know the path of a node, and if we want to find out Cherry's path, make a query using its left and right values of 4 and 5来.

SELECT name from the tree WHERE lft < 4 and RGT > 5 order by LfT ASC;
This will result in the following:

+------------+
| name |
+------------+
| Food |
| Fruit |
| Red |
+------------+
So how many offspring nodes does a node have? Very simple, the total number of descendants = (right-left value-1)/2 descendants = (right–left-1)/2 don't believe it? Count yourself. With this simple formula, we can quickly figure out that the "Fruit 2-11" node has 4 descendants, and the "Banana 8-9" node has no descendants, which means it is not a parent node.
Isn't it amazing? Although I have used this method many times, I still feel amazing every time I do it.

This is a good idea, but is there any way to help us create a data table with the right and left values? Here's another function for you, which automatically converts a table of name and parent structures to a data table with a left-and-right value.

<?php
function Rebuild_tree ($parent, $left) {
The right value of this node was the left value + 1
$right = $left +1;

Get all children the 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 ' right ' value, which is
Incremented by the Rebuild_tree function
$right = Rebuild_tree ($row [' name '], $right);
}

We ' ve got the left value, and now 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 to right value of this node + 1
return $right +1;
}
?>
Of course this function is a recursive function, we need to start from the root node to run this function to reconstruct a tree with the left and right values

Rebuild_tree (' Food ', 1);
The function looks a bit complicated, but it does the same thing as manually numbering a table, which converts a three-dimensional multi-layer structure into a data table with a left and right value.

So how do we add, update, and delete a node for such a structure? There are generally two ways to add a node:

Retains the original name and parent structure, adds data to the data in the old way, and then uses the Rebuild_tree function to renumber the entire structure once each additional piece of data is added.
A more efficient approach is to change all the values that are located to the right of the new node. For example: We want to add a new fruit "strawberry" (strawberry) it will become the last child node of the "Red" node. First we need to make room for it. The right value of "Red" should be changed from 6 to 8, and the value of "yellow 7-10" should be changed to 9-12. By analogy, we can see that if we want to make room for the new value, we need to add 2 to all nodes with a value greater than 5 (5 is the right value of the last child node of "Red"). So we do database operations like this:

UPDATE Tree SET rgt=rgt+2 WHERE rgt>5;
UPDATE Tree SET lft=lft+2 WHERE lft>5;
This frees up space for the newly inserted value, and now you can create a new data node in the vacated space, with a value of 6 and 7, respectively.

INSERT into the tree SET lft=6, rgt=7, name= ' strawberry ';

Check it again! What do you think? It's going to be quick.
OK, now you can design your multilevel database structure in two different ways, depending on your personal judgment, but I prefer the second method for a large number of hierarchies. The first method is easier if you have smaller queries but need to add and update data frequently.

In addition, if the database is supported, you can also write Rebuild_tree () and space-making operations into the database-side trigger functions, which are automatically executed at the time of insertion and update, so that you can get better operational efficiencies, and your SQL statements for adding new nodes will become simpler.
Recursive method of class recursion
Posted by visitor on, May 31-9:18am.
I wrote a procedure with the recursive method, which is not exactly the same as the recursion in the article.
Preparing to migrate to Xoops:
Http://dev.xoops.org/modules/xfmod/project/?ulink

Memory overflow has occurred
But I'm going to continue to use recursion, but we need to continue to improve

I'd like to have a chance to talk to you about CMS
»reply to this comment
Or the comparison of two methods
Posted by visitor on, March 17-8:30pm.
After a careful study of this article, feel the benefit is not shallow, but then think, think there is a problem (for good memory, adjacent directory mode I called recursive method, the pre-sorted traversal tree algorithm I called the method of the pre-ordered tree):

1, the difference between the two methods is that recursion is the time to use the stack in the query recursion, the pre-sorted tree is to update the node to half (refers to the second half of the inserted node) node update. Although you also said that if there are more nodes, update frequently, the efficiency of the pre-sorting tree will be reduced, the use of recursion will be better, and if the node hierarchy is more, first recursion will lead to stack overflow, and furthermore, the efficiency of recursion itself is not high, plus each level of recursion to operate the database, the overall effect will not be ideal. My current practice is to get all the data out at once, and then recursively manipulate the array, will be better; if you make further improvements, you can add a root root node for each row of records (currently recording only adjacent parent nodes), so it is more efficient to find the branch tree, and it is also very convenient to update the tree. It should be a better way.

2, improve the recursive way, in the article in the calculation of the pre-sorted tree node in the left and right time in fact also used a way of traversal, by replacing the stack with an array, the stack is manually implemented and ejected; This method can also improve the efficiency of recursion if it is referred to the recursive algorithm and an array is substituted for the stack when recursion is made.

3, concurrency, if the concurrency is taken into account, especially when the tree is updated, the method of large Area update node information in the pre-sorted tree needs additional attention to the use of lock and transaction mechanism to ensure data consistency.

4, multiple root nodes or multiple parent nodes, in this case, it is obviously not a standard two-fork tree or multiple fork tree, the pre-ordered tree algorithm needs to be improved to adapt to, and the recursive method is easy to apply, so in this case, the adaptability of the recursion is strong. This is of course, because the recursive method is a form of the list, trees, graphs can be expressed by the linked list, of course, adaptable.

5, intuitive, if not program operation, directly observe the data stored in the database, it is obvious that the recursive way of storing data is more intuitive, and the pre-sorted tree data is difficult to read directly (for hierarchical relations), this in the data exchange will have an impact?

Overall, I personally prefer to use the recursive method, but always worried about the effect of recursion on efficiency, fortunately has not touched the larger classification level, recursive array substitution stack is a better way to improve. The pre-sorted tree is an efficient way to solve simple trees, and it should be very good with custom, especially it is convenient to reverse lookup from leaf node to root node.

Fwolf
www.fwolf.com
»reply to this comment
very pleased to see your reply
Posted by Shuke on, March 18-5:47am.
I'm very glad that you have finished reading this article so seriously. This article is actually originally published in the sitepoint.com, I translated it, I hope to beginners to introduce some of the introduction of methods, a primer. Your method is also very good, have a chance I will try. (If you're interested, why don't you write a tutorial on your method and your implementation code in the above example?) so you can use a more practical example to imitate it. If you are interested in studying the database to preserve the multilevel structure, there are two connections here that are good for reference:
Introduces the common 4 methods
once query, array sort script I think your script is definitely better than this.
In addition, I see you also use Drupal, it also has an advanced feature called Distributed user authentication system, as long as any Drupal site registration can be logged in to visit other Drupal site. It's very interesting.
Good luck!
»reply to this comment
has implemented the
Posted by visitor on, March 25-10:10pm with loops.
I have already read the information you provided last time, but honestly, there aren't too many new things in the first article, maybe I didn't see it too well, the second one is actually written by PHP3, the program structure is not looked at, use too many functions to cross.
I'm in a system where the user role needs to be graded, according to the idea of the array of traversal written down, there is no time to organize, first put here you see it, the database is ADODB, the program is directly from the system, want to be able to describe clearly, mainly using the PHP powerful array operation, Use loops for recursion. The annotation is a similar method, but the timing of the processing of the results is different.

<?php
/**
* Display list
* @access Public
*/
function Displist ()
{
How to display without indentation
$this->misdisplistindex = true;
Echo (' <p align= "right" ><a href= "Action=new&part=role" > Add new Role </a> </p>); _fcksavedurl= "" Action=new&part=role "> Add new Role </a> </p>");
//
$this->mlisttitle = ' user role list ';
$this->setdataoption (' list ');
//
$this->setquerytable (Array ($this->mtableuserrole));
//
Query order
$this->setqueryorder (' ASC ', $this->mtableuserrole, ' sequence ');
//
$this->query (' list ');
Parent::D isplist ();

Another way to display, using an array as the stack, a: the stack when the role, press the end of the delete source
$this->checkproperty (' Mrdb ');
$this->checkproperty (' Mrsql ');
$this->mrsql->select (' role, title, parent ');
$this->mrsql->from ($this->mtableuserrole);
$this->mrsql->orderby (' Parent, sequence ');
$this->mrs = $this->mrdb->execute ($this->mrsql->sql ());
if (0 < count ($this->mrs))
// {
$source = & $this->mrs->getarray (); Digital Index
$stack = Array ('); Stack
$stacki = Array (-1); and stack corresponds to the level of data in the tree in the record stack
$target = Array ();
while (0 < count ($stack))
// {
$item = Array_shift ($stack);
$lev = Array_shift ($stacki);
if (!empty ($item))
// {
Where the processed data is placed in the target array.
Array_push ($target, Str_repeat (", $lev). $item);
$s 1 = str_repeat (", $lev). $item;
// }
$del = Array (); The node to remove from the $source
$ar = Array (); Nodes that need to be added to the stack
foreach ($source as $key => $val)
// {
Find a matching child node
if (empty ($item))
// {
$find = Empty ($source [$key] [' parent ']);
// }
Else
// {
$find = ($item = = $source [$key] [' parent ']);
// }
if ($find)
// {
Array_unshift ($ar, $source [$key] [' role ']);
$del [] = $key;
// }
// }
foreach ($ar as $val)
// {
Array_unshift ($stack, $val);
Array_unshift ($stacki, $lev + 1);
// }
foreach ($del as $val)
// {
Unset ($source [$val]);
// }
Echo (Implode (', ', $stack). ' <br/> '. Implode (', ', $stacki). ' <br/> '. Implode (', ', $target). ' <br/><br/> ');
// }
Debug_array ();
// }
Else
// {
Echo (' <center> not retrieving Data </center> ');
// }

Another way to display, using an array as the stack, B: Stack time to save the index of the array, out of the stack and then delete the source after use
$this->checkproperty (' Mrdb ');
$this->checkproperty (' Mrsql ');
$this->mrsql->select (' role, title, parent ');
$this->mrsql->from ($this->mtableuserrole);
$this->mrsql->orderby (' Parent, sequence ');
$this->mrs = $this->mrdb->execute ($this->mrsql->sql ());
if (!empty ($this->mrs) &&! $this->mrs->eof)
{
$source = & $this->mrs->getarray (); Digital Index
$stack = Array (-1); Stack
$stacki = Array (-1); and stack corresponds to the level of data in the tree in the record stack
$target = Array ();
while (0 < count ($stack))
{
$item = Array_shift ($stack);
$lev = Array_shift ($stacki);
if ( -1!= $item)
{
Where the processed data is placed in the target array.
$s 1 = str_repeat (", $lev). ' <a href= '? action=disp&part=role&role= '. $source [$item] [' role ']. ' > '. $source [$item] [' title ']. ' </a> ';
$s 2 = ' <a href= ' action=edit&part=role&role= '. $source [$item] [' role ']. ' > Editor </a> <a href= '? action=delete&part=role&role= '. $source [$item] [' role ']. ' > Delete </a> ';
Array_push ($target, Array ($s 1, $s 2));
}
$del = Array (); The node to remove from the $source
$ar = Array (); Nodes that need to be added to the stack
foreach ($source as $key => $val)
{
Find a matching child node
if ( -1 = $item)
{
$find = Empty ($source [$key] [' parent ']);
}
Else
{
$find = ($source [$item] [' role '] = = $source [$key] [' parent ']);
}
if ($find)
{
Array_unshift ($ar, $key);
}
}
foreach ($ar as $val)
{
Array_unshift ($stack, $val);
Array_unshift ($stacki, $lev + 1);
}
Remove from source
Unset ($source [$item]);
Echo (Implode (', ', $stack). ' <br/> '. Implode (', ', $stacki). ' <br/> '. Implode (', ', $target). ' <br/><br/> ');
}
Output
Echo (' <p align= "right" ><a href= "Action=new&part=role" > Add new Role </a> </p>);
Array_unshift ($target, Array (' role ', ' operation '));
$this->checkproperty (' mrlt ');
$this->mrlt->setdata ($target);
$this->mrlt->mlisttitle = ' user role list ';
$this->mrlt->misdispindex = false;
$this->mrlt->disp ();
}
Else
{
Echo (' <center> not retrieving Data </center> ');
}
}//End of function displist
?>