Php: tree structure algorithm

Php: tree structure algorithm. Read the php: tree structure algorithm, which is reprinted from The Joy village. I have read this article before and told you quite clearly. 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 the application of PHP, it is usually relational to provide back-end data storage "> <LINKhref =" http://www.php100.c

I have read this article from the village of joy and told it clearly.
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)
Pre-sorted traversal tree algorithm (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. Here I use a simple food directory as our sample data. Our data structure is as follows:

Food
|
| --- Fruit
|
| --- Red
|
| -- Cherry
|
| --- Yellow
|
| -- Banana
|
| --- Meat
|
| -- Beef
|
| -- Pork
To take care of PHP fans who are confused about English

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

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:

+ ----------------------- +
| 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, description. With such a table, we can store the entire multi-level tree structure through the database.

Show multi-level tree
If we need to display such a multi-level structure, we need a recursive function.

// $ 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:

Food
Fruit
Red
Cherry
Yellow
Banana
Meat
Beef
Pork
If you only want to display a part of the entire structure, for example, the fruit part, you can call it as follows:

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 can query the Red parent node and add it to the path, and so on until "Food" at the highest level"

// $ 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:

Array
(
[0] => Food
[1] => Fruit
[2] => Red
)
How to print it into the desired format is yours.
Disadvantage: 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.

Now let's take a look at another method that does not use recursive computing and is faster. this is the modified preorder tree traversal algorithm method, which is rarely used, the first use is not as easy to understand as the above method, but because this method does not use recursive query algorithms, it has a 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 will try again. please 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.

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.

+ ----------------------- + ----- +
| 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 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.

+ ------------ + ----- +
| 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: SELECT * FROM tree WHERE lft BETWEEN 2 AND 11; the query returns the following results.

+ ------------ + ----- +
| 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:

SELECT * FROM tree WHERE lft BETWEEN 2 AND 11 order by lft ASC;
The remaining questions show how to indent the hierarchy.

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.

SELECT name FROM tree WHERE lft <4 AND rgt> 5 order by lft ASC;
The following result is displayed:

+ ------------ +
| Name |
+ ------------ +
| Food |
| Fruit |
| Red |
+ ------------ +
How many child nodes does a node have? Very simple. do you believe the total number of descendants = (right-left-1)/2 descendants = (right-left-1)/2? 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.

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.

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:

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.
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:

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.

Insert into tree SET lft = 6, rgt = 7, name = 'Strawberry ';

Make another query! How is it? Soon.
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.
Class recursion
Posted by guest on 2004, May 31-am.
I used the class recursion method to write a segment program, which is not exactly the same as recursion in the article.
Preparing to port to xoops:
Http://dev.xoops.org/modules/xfmod/project? Ulink

Memory overflow
However, we are going to continue using the recursive method, but we only need to continue to improve it.

I hope to have the opportunity to discuss cms with you.
» Reply to this comment
Comparison between the two methods
Posted by guest on 2004, March 17-pm.
After carefully studying this article, I felt that the benefit was not too great, but later I thought about it and thought that there was a problem (for good memory, the method adjacent to the directory pattern is called recursion, pre-sorting traversal tree algorithm (called pre-sorting tree ):

1. The difference between the two methods is that recursion uses stacks for recursion during query, the pre-sorting tree is to update half of the nodes when updating the node (that is, the half of the inserted node. Although you have also said that if there are more nodes and frequent updates, the efficiency of the pre-sorting tree will be reduced and recursion will be better. if there are more nodes, first, recursion will cause stack overflow, and recursion itself will not be efficient. In addition, each layer of recursion will have to operate on the database, and the overall effect will not be ideal. My current practice is to extract all the data at a time and then perform recursive operations on the array, which will be better. if we further improve the function, you can add a ROOT node for each row of records (currently, only adjacent parent nodes are recorded), so that the search for branch trees has a high aging rate, it is also very convenient to update the tree. it should be a good method.

2. improved recursion. in this article, we also used a traversal method when calculating the left and right values of the pre-sorting tree node. we used arrays to replace stacks and manually implemented stack pressure and pop-up; this method can also improve the efficiency of recursion if it is referenced in a recursive algorithm and uses arrays instead of stacks during recursion.

3. concurrency. if concurrency is taken into account, especially when updating the tree, the pre-sorting tree needs to use the locking and transaction mechanism to update node information in a large area to ensure data consistency.

4. in the case of multiple root nodes or multiple parent nodes, it is obviously not a standard binary tree or multi-Cross Tree, the pre-sorting tree algorithm must be greatly improved to adapt, while the recursive method can be applied freely. in this case, the recursive method is highly adaptive. This is of course, because the recursive method is a form of linked list. trees and graphs can all be expressed by linked lists. of course, the method is adaptable.

5. intuitive. if you directly observe the data stored in the database without using a program, it is obvious that the data stored in recursive mode is more intuitive, the data in the pre-sorting tree is difficult to read directly (for hierarchical relationships). does this affect data exchange?

In general, I personally prefer to use recursive methods, but I have been worried about the impact of recursion on efficiency. Fortunately, I have not touched on large-scale classification layers, recursive replacement of stacks with arrays is a good improvement method. The pre-sorting tree is an efficient way to solve the simple tree. It should also be very good when used to it, especially its reverse search from the leaf node to the root node is very convenient.

Fwolf
Www.fwolf.com
» Reply to this comment
We are very glad to see your reply.
Posted by shuke on 2004, March 18-am.
I'm glad that you have read this article so seriously. This article was originally published on sitepoint.com. I have translated it and hope to introduce some methods for beginners. Your method is also good. I will try it if I have the chance. (If you are interested, why not write your method and specific implementation code as a tutorial in the above example, so that you can use more practical examples to imitate it) if you are interested in studying the multi-level structure stored in the database, there are two connections that are also good for reference:
Describes the common method in step 4.
For a single query, the script for sorting arrays is definitely better than this one.
In addition, I can see that you also use drupal. it also has a advanced feature called distributed user verification system. once registered on any drupal site, you can log on to other drupal sites. Very interesting.
Good luck!
» Reply to this comment
It has been achieved through loops.
Posted by guest on 2004, March 25-pm.
I have read all the information you provided last time, but to be honest, there are not many new things in the first article. maybe I didn't read them clearly, and the second one was written in PHP3, the program structure is not detailed, and too many functions are used.
In a system, the user role uses classification. the traversal is written according to the array idea and there is no time to sort it out. let's take a look here. The database uses ADODB, the program is directly extracted from the system, hoping to be clearly described. it mainly utilizes PHP's powerful array operations and uses loops for recursion. There is a similar method in the annotation, but the timing of processing results is different.

/**
* Display List
* @ Access public
*/
Function DispList ()
{
// Display mode without indentation
// $ This-> mIsDispListIndex = true;
// Echo ('

Add a new role

'); _ Fcksavedurl = ""? Action = new & part = role "> Add a new role

');"
//
// $ 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: DispList ();

//// Another display method, which uses arrays as the stack. A: stores the role when the stack is pressed, and deletes the 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 (); // numeric index
// $ Stack = array (''); // stack
// $ Stacki = array (-1); // corresponds to the stack and records the layers of data in the stack.
// $ Target = array ();
// While (0 <count ($ stack ))
//{
// $ Item = array_shift ($ stack );
// $ Lev= array_shift ($ stacki );
// If (! Empty ($ item ))
//{
/// Put the processed data in the target array
// Array_push ($ target, str_repeat ('', $ lev). $ item );
/// $ S1 = str_repeat ('', $ lev). $ item;
//}
// $ Del = array (); // The node to be deleted from $ source
// $ Ar = array (); // node to be added to the stack
// Foreach ($ source as $ key => $ val)
//{
//// Search for matched subnodes
// 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, $ EV + 1 );
//}
// Foreach ($ del as $ val)
//{
// Unset ($ source [$ val]);
//}
// Echo (implode (',', $ stack ).'
'. Implode (', ', $ stacki ).'
'. Implode (', ', $ target ).'

');
//}
// Debug_array ();
//}
// Else
//{
// Echo ('

No data retrieved ');
//}

// Another display method. array is used as the stack. B: stores the array index when the stack is pressed. after the stack is used out, the source is deleted.
$ 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 (); // numeric index
$ Stack = array (-1); // stack
$ Stacki = array (-1); // corresponds to the stack, which records the layers of data in the stack in the tree.
$ Target = array ();
While (0 <count ($ stack ))
{
$ Item = array_shift ($ stack );
$ Lev= array_shift ($ stacki );
If (-1! = $ Item)
{
// Put the processed data in the target array
$ S1 = str_repeat ('', $ eV).''. $ source [$ item] ['title']. '';
$ S2 = 'edit delete ';
Array_push ($ target, array ($ s1, $ s2 ));
}
$ Del = array (); // node to be deleted from $ source
$ Ar = array (); // node to be added to the stack
Foreach ($ source as $ key => $ val)
{
// Search for matched subnodes
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, $ EV + 1 );
}
// Delete from source
Unset ($ source [$ item]);
// Echo (implode (',', $ stack ).'
'. Implode (', ', $ stacki ).'
'. Implode (', ', $ target ).'

');
}
// Output
Echo ('

Add a new role

');
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 (' No data retrieved ');
}
} // End of function DispList
?>