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.
