Shulou(Shulou.com)06/03 Report--

This article shows you two ways of PHP and Mysql tree structure design, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

There are two common methods:

1. The way to pick up the table

two。 Preordered traversal tree mode

Suppose the tree structure is as shown below:

Pick-up table mode

It mainly depends on a parent field, which is used to point to the superior node and connect the adjacent superior and subordinate nodes. Id is automatically incremented automatically, and parent_id is the id of the parent node. It is clear at a glance that "Java" is a child of "Language".

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

The copy code is as follows:

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 frequently used function is to get the node path, that is, to give a node and return the path from the root node to the current node. The implementation with functions is as follows:

The copy code is as follows:

To get the path to "MySQL 5.0", call get_path (4), which is the id of this node.

The advantage of receiving tables is that they are easy to understand and the code is relatively simple and clear. The disadvantage is that the recursive SQL query will lead to a large load, especially when you need to deal with a relatively large tree structure, the query statement will increase with the increase of hierarchy, and the bottleneck of WEB application is basically in the database, so this is a fatal disadvantage, which directly leads to many difficulties in the expansion of the tree structure.

Sort traversal tree mode

Now let's talk about the second way, ─ preordered traversal tree mode (commonly known as 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 order of the nodes, as shown in the following figure:

From the root node, the left side is 1, then the left side of the next node is 2, and so on, after the lowest node, the right side of the lowest node is the number on its left plus 1. Following these nodes, we can easily traverse the entire tree. According to the figure above, we made some changes to the data table by adding two fields, lft and rgt to store left and right numbers (since left and right are reserved words for MySQL, we use abbreviations instead). The contents of the rows in the table become:

Next, let's see how simple it is to show the tree / subtree. All you need is a SQL statement. For example, to display the "Database" subtree, you need to get the left and right number of "Database", which is 2 on the left and 11 on the right. Then the SQL statement is:

The copy code is as follows:

SELECT * FROM tree WHERE lft BETWEEN 2 AND 11

The SQL statement is simple, but the indented display we want is a problem. When should indentation be displayed? How many units are indented? To solve this problem, you need to use a stack, that is, last-in, first-out (LIFO), and push the number to the right of each node into the stack. We know that the value on the right side of all nodes is smaller than that on the right side of its parent node, so compare the value on the right side of the current node with the value on the top right of the stack. If the current node is smaller than the value on the top of the stack, it means that the rest of the stack is the parent node. At this time, indentation can be displayed, and the number of elements in the stack is the indent depth. The PHP code is implemented as follows:

The copy code is as follows:

Get the whole tree call show_tree (), get the "Database" subtree call show_tree (2). In this function, we finally don't need to use recursion, hehe.

The next step is to show the path from the root node to a node, which is much easier than the connection table. All you need is a SQL, and there is no need for recursion, such as getting the path to the node "ORACLE". If the left and right values are 7 and 10 respectively, the SQL statement is:

The copy code is as follows:

SELECT name FROM tree WHERE lft = 10 ORDER BY lft ASC

The PHP function is implemented as follows:

The copy code is as follows:

Now that you have no problem displaying the tree and path, you need to know how to insert a node. Before inserting a new node, we first need to make room for this node. Suppose we now want to add a "ORACLE 10" to the right of the "ORACLE 9i" node, the SQL statement to vacate the position is as follows (the right value of "ORACLE 9i" is 9):

The copy code is as follows:

UPDATE tree SET rgt=rgt+2 WHERE rgt > 9

UPDATE tree SET lft=lft+2 WHERE lft > 9

The location is free, so let's start inserting new nodes:

The copy code is as follows:

INSERT INTO tree SET lft=10, rgt=11, name='ORACLE 10'

Call show_tree () to see if the result is for the specific PHP implementation code, which is not written here.

The above contents are the two ways of PHP and Mysql tree structure design. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.