Example of two methods for designing a PHP + Mysql tree structure (unlimited classification) Database

This article mainly introduces two methods of designing a PHP + Mysql tree structure (unlimited classification) database, as well as analysis and discussion of advantages and disadvantages. For more information, see

This article mainly introduces two methods of designing a PHP + Mysql tree structure (unlimited classification) database, as well as analysis and discussion of advantages and disadvantages. For more information, see

We often need to store some tree-structured data in relational databases, such as classification, menus, and forum post tree replies. There are two common methods:

1. Method of receiving tables;

2. Pre-sort the traversal tree method;

Assume that the tree structure is as follows:

Receiving table

It mainly depends on a parent field, which is used to point to the upper-level node and connect adjacent upper and lower-level nodes. The id is auto-incrementing, And the parent_id is the id of the upper-level node. "Java" is a sub-node of "Language.

We need to display the tree. PHP code can also be very intuitive. The Code is as follows:

The Code is as follows:

<? Php
/**
* Obtain all child nodes under the parent node
*
* @ Since 2011-05-18
*
* @ Param $ parent_id parent node id. If the value is 0, the entire tree structure is displayed.
* @ Param $ level the level of the current node, used to indent the display node.
* @ Return void
*/
Function show_children ($ parent_id = 0, $ level = 0)
{
// Obtain all child nodes under the parent node
$ Result = mysql_query ('select id, name FROM tree WHERE parent_id = '. intval ($ parent_id ));
// Display each subnode
While ($ row = mysql_fetch_array ($ result )){
// Indent display
Echo'

'. $ Row ['name'].'

';
// Call the current function recursively and display the subnodes at the next level
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 "Database" id is 2.

Another frequently used function is to obtain the Node path, that is, to give a node and return the path from the root node to the current node. Function implementation:

The Code is as follows:

<? Php
/**
* @ Param $ id: the id of the current node in the path to be obtained.
* @ Return array
*/
Function get_path ($ id)
{
// Obtain the parent node id and name of the current node
$ Result = mysql_query ('select parent_id, name FROM tree wherename'];
// If the parent node is not 0, that is, a non-root node, recursive call is performed to obtain the path of the parent node.
If ($ row ['parent _ id']) {
// Recursive call to obtain the path of the parent node and merge it to the front edge of other elements in the current path Array
$ Path = array_merge (get_path ($ row ['parent _ id']), $ path );
}
Return $ path;
}
?>

To obtain the path "MySQL 5.0", call get_path (4) and 4 is the id of this node.

The advantages of the table picking method are that it is easy to understand and the code is relatively simple. The disadvantage is that recursive SQL queries will increase the load, especially when a large tree structure needs to be processed, the query statements will increase as the hierarchy increases, the bottlenecks of WEB applications are basically in the database aspect. Therefore, this is a fatal drawback, which directly leads to difficulties in tree structure expansion.

Sort the traversal tree

Now let's talk about the second method-the pre-sorted Traversal Tree (namely, the MPTT, Modified Preorder Tree Traversal ). Based on the first method, this algorithm adds a left and right number to each node to identify the traversal sequence of the node, as shown in:

From the root node, the left side is 1, then the left side of the next node is 2, and so on. After arriving at the lowest layer node, the right side of the lowest layer node adds 1 to the left side of the node. Following these nodes, we can easily traverse the entire tree. We made some changes to the data table and added two fields. lft and rgt are used to store the left and right numbers (since left and right are reserved words of MySQL, we use the abbreviation instead ). The content of each row in the table is changed:

Next, let's take a look at how simple the display tree/subtree is. You only need an SQL statement. For example, if the "Database" subtree is displayed, You need to obtain the left and right numbers of the "Database, if the value is 2 on the left and 11 on the right, the SQL statement is:

The Code is as follows:

SELECT * FROM tree WHERE lft BETWEEN 2 AND 11;

SQL statements are simple, but the indentation we want is a problem. When should I display indentation? How many units are indented? To solve this problem, you need to use the stack, that is, the number on the right of each node is pushed into the stack. We know that the value on the right of all nodes is smaller than the value on the right of the parent node, so we can compare the value on the right of the current node with the value on the right at the top of the stack, if the current node is smaller than the value at the top of the stack, it indicates that all the remaining nodes in the current stack are parent nodes. In this case, indentation is displayed. The number of elements in the stack is the indentation depth. PHP code implementation is as follows:

The Code is as follows:

<? Php
/**
* @ Param $ root_id the id of the tree/sub-tree root node to be displayed.
*/
Function show_tree ($ root_id = 1)
{
// Obtain the left and right values of the current Root Node
$ Result = mysql_query ('select lft, rgt FROM tree WHERESELECT name, lft, rgt FROM tree WHERE lft '. $ row ['lft ']. AND '. $ row ['rgt ']. 'order BY lft ASC ');
// Display each node of the tree
While ($ row = mysql_fetch_array ($ result )){
If (count ($ stack)> 0) {// detection only when the stack is not empty
// If the value on the right of the current node is greater than the value on the top of the stack, the value on the top of the stack is removed because the node corresponding to this value is not the parent node of the current node.
While ($ row ['rgt ']> $ stack [count ($ stack)-1]) {
Array_pop ($ stack );
} // After the while loop ends, only the parent node of the current node is left in the stack.
}
// Indentation can be displayed now
Echo'

'. $ Row ['name'].'

';
// Press the current node into the stack to prepare for the indent display of the node behind the loop
Array_push ($ stack, $ row ['rgt ']);
}
}
?>

Call show_tree () to obtain the entire tree, and call show_tree (2) to obtain the "Database" subtree ). In this function, we don't need recursion anymore.

The next step is to display the path from the root node to a node, which is much simpler than the method for receiving tables. You only need one SQL statement, the SQL statement is as follows:

The Code is as follows:

SELECT name FROM tree WHERE lft <= 7 AND rgt> = 10 order by lft ASC;

PHP functions are implemented as follows:

The Code is as follows: