PHP: An algorithm for tree-shaped structures

From the joy of the village reprinted, before also read this article, the narrative is still relatively clear.

Product classification, multi-level tree structure of the forum, mailing lists and many other places we all encounter this problem: how to store multi-level structure of data?

In the application of PHP, it is usually a relational database that provides back-end data storage, which can save large amount of data and provide efficient data retrieval and update service. However, the basic form of relational data is a criss-cross table, and it is a planar structure, so if we want to store the multilevel tree structure in the relational database, we need to make a reasonable translation work. Next I will see what I saw and some practical experience and everyone to explore.

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

Adjacent directory mode (adjacency list model)

Pre-sorted traversal tree algorithm (modified preorder tree traversal algorithm)

I am not a computer professional, and have not learned anything about data structure, so these two names are my own in accordance with the literal meaning of flipping, if wrong, please give more advice.

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

Food

|

|---Fruit

| |

| |---Red

| | |

| | | |--cherry

| |

| |---Yellow

| |

| |--banana

|

|---meat

|

|--beef

|

|--pork

In order to take care of those English mess PHP enthusiasts

Food: Foods

Fruit: Fruit

Red: Pink

Cherry: Cherry

Yellow: Yellow

Banana: Banana

Meat: Meat

Beef: Beef

Pork: Pork

Adjacent directory mode (adjacency list model)

This pattern we often use, a lot of tutorials and books are also introduced. We represent the parent node of the node by adding a property parent to each node to 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 see that Pear is a child of green, and green is a sub-node of fruit. and 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 number ID to mark each node, the database table structure should probably like this: ID, parent_id, name, description. With such a table we can save the entire multilevel tree structure through the database.

Show Multilevel Tree

If we need to display such a multilevel structure requires a recursive function.

$parent is the parent of the children we want to see

$level is increased if we go deeper into the tree,

Used to display a nice indented tree

function Display_children ($parent, $level)

{

Get all 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);

}

}

?>

Using this function on the root node (food) of the whole structure can print out the entire multilevel tree structure, because food is the root node of 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 want to show only a portion of the entire structure, such as the fruit section, you can call this:

Display_children (' Fruit ', 0);

With almost the same method, we can know the path from the root node to any node. For example Cherry's path is "Food > Fruit > Red". In order to get such a path we need to start with the deepest first level "Cherry", query to get 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 to the top of the "food"

$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 up query if not the root node

(root node does not have parent node)

if ($row [' Parent ']!= ')

{

The last part of the path to $node, is the name

The $node of the parent

$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 will get an array like this:

Array

(

[0] = food

[1] = Fruit

[2] = Red

)

Then how to print it into the format you want, is your thing.

Cons: This method is simple, easy to understand, good to get started. But there are some drawbacks. Mainly because the running speed is very slow, because each node needs to make a database query, the large amount of data to make a lot of queries to complete a tree. In addition, due to recursive operation, each level of recursion needs to occupy some memory, so the efficiency of space utilization is also relatively low.

Now let's take a look at another faster way of not using recursive calculations, which is that the pre-sort traversal tree algorithm (modified preorder tree traversal algorithm) is less likely to come into contact with first-time use and not as easy to understand as the method above , but because this method does not use the recursive query algorithm, has the higher query efficiency.

We first draw the multilevel data on the paper in the following way, write 1 on the left side of food on the root node and then continue down the tree on the left side of the Fruit to write 2 and then move on, along the edge of the tree to each node labeled with the left and right numbers. The last number is labeled 18 on the right side of food. In this diagram below you can see the entire multilevel structure labeled with the numbers. (not understand?) Use your finger pointing at the number from 1 to 18 to understand what's going on. Do not understand, count again, notice to move your fingers).

These numbers indicate the relationship 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 an rvalue less than 11 are descendants of "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 with left and right values. Before we go on, let's take a look at the data tables we've compiled 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: Since "left" and "right" have special meanings in SQL, we need to use "LfT" and "RGT" to represent the field. 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 the query statement: SELECT * from tree WHERE lft between 2 and 11; This query has 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 like the recursive function above, we also need to sort the query. To sort by the left value of the node:

SELECT * from tree WHERE lft between 2 and one ORDER by LfT ASC;

The remaining question is how to show the level indentation.

function Display_tree ($root)

{

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);

Preparing an empty right-value stack

$right = Array ();

Get all descendant nodes 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 this new function of ours may be faster because there are only 2 database queries. It is even easier to know the path of a node, if we want to understand the path of cherry and use its left and right values of 4 and 5来 to do a query.

SELECT name from tree WHERE lft < 4 and RGT > 5 ORDER by LfT ASC;

This gives you the following results:

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

| name |

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

| Food |

| Fruit |

| Red |

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

So how many descendant nodes does a node have? Very simple, descendants total = (Rvalue-lvalue-1)/2 descendants = (right–left-1)/2 don't believe it? With this simple formula, we can quickly figure out that the "Fruit 2-11" node has 4 descendant nodes, and the "Banana 8-9" node has no descendant nodes, which means it is not a parent node.

It's amazing, isn't it? Although I have used this method many times, I feel amazing every time I do this.

This is a really good idea, but what can be done to help us create a data table that has left and right values? Here is a function that can be used to automatically convert a table of name and parent structure to a data table with left and right values.

function Rebuild_tree ($parent, $left) {

The right value of this node was 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 may seem a bit complicated, but its function is the same as manually numbering the table, which is to convert the stereoscopic multilayer structure into a data table with left and right values.

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

Keep the original name and parent structure, and use the old method to add data to the data, and use 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 values on the right side of the new node. For example: We want to add a new fruit "strawberry" (strawberry) It will be the last child node of the "Red" node. First we need to make some room for it. The 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 we can tell that if you want to make room for the new value, you need to give all nodes with a value greater than 5 (5 is the right value of the last child node of "Red") plus 2. 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 creates a new data node in the vacated space, with the left and right values of 6 and 7, respectively.

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

Let's do another query.

Well, now you can design your multi-level database structure in two different ways, depending on your personal judgment, but I prefer the second approach to a large number of hierarchies. The first method is easier if the query volume is small but the data needs to be added and updated frequently.

In addition, if the database is supported, you can also write Rebuild_tree () and free space operations as a trigger function on the database side, which is executed automatically when inserting and updating, which makes the operation more efficient, and the SQL statement that you add the new node becomes simpler.

Class Recursion method

Posted by guest on 2004, May 31-9:18am.

I wrote a program in class recursion, 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 we're going to continue with recursion, just to keep improving.

Hope to have the opportunity to discuss with you the CMS

»reply to this comment

Or is it a comparison of two methods?

Posted by guest on 2004, March 17-8:30pm.

Careful study of this article, I feel that the benefit is not shallow, but then think about it, think there is a problem (for good memory, adjacent directory mode I called recursive method, pre-sorting traversal tree algorithm I called the pre-sorting tree method):

1, the difference between the two methods is that recursion is to use the stack for recursion when querying, and the pre-order tree is the update of the node when the node is updated to half (refers to the second half of the inserted node). Although you also said, if the node is more, update and frequent, the pre-sorting tree efficiency will be lower, the use of recursion will be better, and if the node level is more, the first recursion will lead to stack overflow, and the recursive itself is not high efficiency, plus each layer of recursion to operate the database, the overall effect will not be ideal. My current practice is to take the data all at once, and then recursive operation of the array, it will be better, and further improvements, you can add a root root node for each row of records (currently only the adjacent parent node), so that in the search branch tree efficiency is relatively high, update the tree is also very convenient, Should be a better way.

2, improve the way of recursion, in the article in the calculation of pre-ordered tree node of the left and right value of the time actually also used a traversal way, through the array to replace the stack, manually implement the press stack and pop-up; This method can also improve the efficiency of recursion if it is referred to a recursive algorithm and uses arrays instead of stacks when recursive

3, concurrency, if considering the concurrency, especially when updating the tree, the pre-sorting tree Large area Update node information method requires additional attention to the use of locking and transaction mechanisms to ensure data consistency.

4, multiple root node or multi-parent node situation, in this case, it is clearly not a standard two fork tree or multi-fork tree, the pre-sorting tree algorithm needs to be relatively large improvement to adapt, and recursive method is applied freely, so in this case, recursion is stronger. This is of course, because the recursive method is a form of the list, the tree, the graph can be used to express the list, of course, the adaptability is strong.

5, intuitive, without program operation, directly observe the data stored in the database, it is obvious that the recursive way to store the data is more intuitive, and the pre-sorting tree data is difficult to read directly (for the hierarchical relationship), which in the data exchange will have an impact?

Generally speaking, I prefer to use recursive method, but always worry about the effect of recursion on efficiency, fortunately has not touched the scale of the classification hierarchy, recursive array substitution stack is a better way to improve. The pre-sorting tree is an efficient way to solve a simple tree, and it should also be very good, especially if it is very convenient to find the reverse from the leaf node to the root node.

Fwolf

Www.fwolf.com

»reply to this comment

I'm very glad to see your reply.

Posted by Shuke on 2004, March 18-5:47am.

I'm very glad that you have read this article so earnestly. This article is originally published in the sitepoint.com, I translated it, I hope to give beginners hope to introduce some of the methods of introduction. Your method is also very good, I have the opportunity to try. (If you're interested, why not put your method and implementation code in the tutorial for the example above, so you can imitate it with more practical examples) if you are interested in storing multilevel structures in a database, there are also two connections that are good for reference:

Describes the common 4 methods

One query, array sort script I think your script is definitely stronger than this.

In addition, I see you also use Drupal, it also has an advanced function called Distributed user authentication system, as long as any one Drupal site after registration can login to visit other Drupal sites. It's very interesting.

Good!

»reply to this comment

Using loops to make a contribution has been achieved.

Posted by guest on 2004, March 25-10:10pm.

I have already read the information you provided last time, but frankly speaking, the first article did not have too many new things, perhaps I did not see too understand, the second incredibly is PHP3 write, the program structure did not look closely, with too many functions cross.

I am in a system of user role to use the classification, according to the idea of the array to write down the traversal, no time to organize, first put here you see, the database is ADODB, the program is directly from the system picked out, hope to be able to describe clearly, mainly using PHP powerful array operation, Recursive using loops. Comments are a similar approach, but the timing of the results is different.

/**

* Display list

* @access Public

*/

function Displist ()

{

How to display without indentation

$this->misdisplistindex = true;

Echo ('

//

$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, use an array as a stack, A: Save a role when the stack is pressed, delete the source when it is finished

$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, recording the hierarchy of the data in the tree 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 here

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)

// {

Finding 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). ‘

‘ . Implode (', ', $stacki). ‘

‘ . Implode (', ', $target). ‘

‘);

// }

Debug_array ();

// }

Else

// {

Echo ('

No data retrieved

‘);

// }

Another way to display, use an array as a stack, B: Stack The array index, and then delete the source after the stack is exhausted.

$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, recording the hierarchy of the data in the tree in the stack

$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 here

$s 1 = str_repeat (", $lev). ' $s 2 = ' 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)

{

Finding 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). ‘

‘ . Implode (', ', $stacki). ‘

‘ . Implode (', ', $target). ‘

‘);

}

Output

Echo ('

Array_unshift ($target, Array (' role ', ' action '));

$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

?>

Http://blog.sina.com.cn/s/blog_1603d3c730102wgmj.html

Http://blog.sina.com.cn/s/blog_1603d3c730102wgmk.html

Http://blog.sina.com.cn/s/blog_1603d3c730102wgmn.html

Http://blog.sina.com.cn/s/blog_1603d3c730102wgmo.html

Http://blog.sina.com.cn/s/blog_1603d3c730102wgmj.html

Http://blog.sina.com.cn/s/blog_1603d3c730102wgmk.html

Http://blog.sina.com.cn/s/blog_1603d3c730102wgmn.html

Http://blog.sina.com.cn/s/blog_1603d3c730102wgmo.html

PHP: Algorithm for tree structure