2 Ways of Php+mysql tree structure (infinite classification) database design examples _php skills

We often need to keep some tree-like structure data in the relational database, such as classification, menu, forum post tree reply and so on. There are two common methods:

1. The way of receiving the table;

2. Pre-ordered traversal tree mode;

Suppose the tree structure looks like this:

Collar Table Mode

Mainly rely on a parent field, used to point to the superior node, the adjacent subordinate nodes, the ID is automatically incremented automatically, parent_id for the ancestor Node ID. At a glance, "Java" is a child node of "Language".

We want to display the tree, the PHP code can also be very intuitive, the code is as follows:

Copy Code code as follows:

<?php
/**
* Get all child nodes under the parent node
*
* @since 2011-05-18
*
* @param $parent _id parent node id,0 Displays the entire tree structure.
* @param $level the level at which the current node is being indented for the display node.
* @return void
*/
function Show_children ($parent _id = 0, $level = 0)
{
Gets all child nodes under the parent node
$result = mysql_query (' SELECT ID, name from tree WHERE parent_id= '. Intval ($parent _id));
Show each child node
while ($row = Mysql_fetch_array ($result)) {
Indent display
Echo ' <div style= ' margin-left: '. ($level * 12). ' px ' > '. $row [' name ']. ' </div> ';
Recursively calls the current function, displaying the next level of child nodes
Show_children ($row [' id '], $level + 1);
}
}
?>

To display the entire tree structure, call Show_children (). To display the "database" subtree, call Show_children (2) because the ID of "database" is 2.

Another feature that is often used is to get the node path, which is to give a node that returns the path from the root node to the current node. Use the function to achieve the following:

Copy Code code as follows:

<?php
/**
* @param the ID of the current node $id need to get the path.
* @return Array
*/
function Get_path ($id)
{
Gets the parent node ID of the current node and the current section roll name
$result = mysql_query (' SELECT parent_id, name from tree WHERE id= '. Intval ($id));
$row = Mysql_fetch_array ($result);
Use this array to save the path
$path = Array ();
Saves the current section name into an array of paths
$path [] = $row [' name '];
If the parent node is not 0, or is not a root node, a recursive call is made to get the path of the parent node
if ($row [' parent_id ']) {
Recursive invocation, gets the path of the parent node, and merges into the front of the other elements of the current path array
$path = Array_merge (Get_path ($row [' parent_id ']), $path);
}
return $path;
}
?>

To get the "MySQL 5.0" path, call Get_path (4), and 4 is the ID of this node.

The advantage of the collar form is that it is easy to understand, and the code is simpler and more straightforward. The disadvantage is that the SQL query in recursion can cause the load to become larger, in particular, the need to deal with large tree-like structure, query statements will increase with the level of increase, the WEB application bottlenecks are basically in the database, so this is a relatively fatal shortcoming, directly lead to the expansion of tree structure difficulties.

Sort Traversal Tree method

Now let's talk about the second way--the pre-sorted traversal tree approach (that is, the commonly referred to mptt,modified preorder traversal). This algorithm is based on the first method, adding a left and right number for each node to identify the traversal order of the nodes, as shown in the following illustration:

From the root node to the left is 1, then the left of the next node is 2, and so on, to the lowest level node, the lowest level node to the right of the left side of the number plus 1. Along these nodes, we can easily traverse through the entire tree. According to the graph above, we make some changes to the datasheet, add two fields, LFT and RGT are used to store the left and right digits (since left and the is the MySQL reserved word, so we use shorthand). The contents of each row in the table become:

Next look at how simple it is to show the tree/subtree, just a single SQL statement, such as displaying the "database" subtree, you need to get the left and right digits of "database", 2 to 11, and then the SQL statement is:

Copy Code code as follows:

SELECT * from tree WHERE lft BETWEEN 2 and 11;

The SQL statement is simple, but the indentation we want to display is a problem. When should I show indents? How many units are indented? To solve this problem, you need to use the stack, which is the LIFO (LIFO), and each node to the right of the number on the stack. We know that the value on the right side of all nodes is smaller than the value to the right of the parent node. Then compare the value on the right side of the current node with the rightmost value on the top of the stack, and if the current node is smaller than the top of the stack, meaning that the current stack is left with the parent node, you can show indents, and the number of elements in the stack is the indentation depth. The PHP code is implemented as follows:

Copy Code code as follows:

<?php
/**
* @param $root _id the tree/Zishugen node ID to display.
*/
function Show_tree ($root _id = 1)
{
Gets the left and right values of the current root node
$result = mysql_query (' SELECT lft, rgt from tree WHERE id= '. Intval ($root _id));
$row = Mysql_fetch_array ($result);
Stack, the value on the right side of the storage node, used to display indents
$stack = Array ();
Gets all descendant nodes of the $root _id node
$result = mysql_query (' SELECT name, LFT, RGT from tree WHERE lft BETWEEN '. $row [' LfT ']. ' and '. $row [' RGT ']. ' ORDER by LfT ASC ');
Show each node of the tree
while ($row = Mysql_fetch_array ($result)) {
if (count ($stack) >0) {//is detected only when the stack is not empty
If the value on the right side of the current node is larger than the top of the stack, the topmost value of the stack is removed, because the node that corresponds to this value is not the parent node of the current node
while ($row [' RGT '] > $stack [Count ($stack)-1]) {
Array_pop ($stack);
After the end of the//while loop, only the parent node of the current node is left in the stack
}
Now you can show indents.
Echo ' <div style= ' margin-left: '. (Count ($stack) *12). ' PX ">". $row [' name ']. ' </div> ';
Press the current node inside the stack to prepare for the indented display of the node behind the loop
Array_push ($stack, $row [' RGT ']);
}
}
?>

Gets the entire tree call Show_tree (), gets the "Database" Subtree call Show_tree (2). In this function, we finally do not need to use the recursion, hehe.

The next step is to show the path from the root node to a node, which is a lot simpler than the picking table, which requires just one SQL line, no recursion, such as getting the path to the "ORACLE" node with the left and right values of 7 and 10, then the SQL statement is:

Copy Code code as follows:

SELECT name from the tree WHERE lft <= 7 and RGT >= the order by LfT ASC;

PHP functions are implemented as follows:

Copy Code code as follows:

<?php
/**
* @param $node _id the node ID needed to get the path
*/
function Get_path2 ($node _id) {
Gets the left and right values of the current node
$result = mysql_query (' SELECT lft, rgt from tree WHERE id= '. Intval ($node _id));
$row = Mysql_fetch_array ($result);
Get all nodes in the path
$result = mysql_query (' SELECT name from tree WHERE lft <= '. $row [' LfT ']. ' and Rgt >= '. $row [' RGT ']. ' ORDER by LfT ASC ');
$path = Array ();
while ($row = Mysql_fetch_array ($result)) {
$path [] = $row [' name '];
}
return $path;
}
?>

It's okay to show the tree and the path, now you need to know how to insert a node. Before inserting a new node, first make room for this node, assuming that we are now adding an Oracle 10 to the right of the Oracle 9i node, the following SQL statement (the right value for Oracle 9i is 9):

Copy Code code as follows:

UPDATE Tree SET rgt=rgt+2 WHERE rgt>9;
UPDATE Tree SET lft=lft+2 WHERE lft>9;

The location is empty, start inserting the new node:

Copy Code code as follows:

INSERT into the tree SET lft=10, rgt=11, name= ' ORACLE 10 ';

Call Show_tree () to see if the result is not specific to the PHP implementation code here is not written.

Now summarize the advantages and disadvantages of the pre-sorted traversal tree method. The disadvantage is that the algorithm is more abstract, not easy to understand, when adding nodes, although only a few SQL statements, but may need to update a lot of records, resulting in blocking. The advantage is the tree construction, the path obtains aspect performance is much better than the collar table way. In other words, this algorithm sacrifices some write performance in exchange for read performance, in the WEB application, the ratio of reading database is much larger than the proportion of the write database, so the pre-sorted traversal tree is more popular than the collar table, more practical, many applications can see MPTT Shadow, usually used in the table have fields LfT and RGT.