Php mysql unlimited classification implementation principle and code

Table Structure:
Copy the Code as follows:
--
-- Table structure 'category'
--
Create table if not exists 'category '(
'Id' int (11) not null AUTO_INCREMENT,
'Type' int (11) not null comment '1 is Article type 2 is product type 3 is download type ',
'Title' varchar (50) not null,
'Lft 'int (11) not null,
'Rgt 'int (11) not null,
'Lorder 'int (11) not null comment' sorting ',
'Create _ time' int (11) not null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8 AUTO_INCREMENT = 10;
--
-- Export the data 'category' In the table'
--
Insert into 'category '('id', 'type', 'title', 'lft', 'rgt ', 'lorder', 'create _ Time') VALUES
(1, 1, 'top-level topic ', 1, 18, 1, 1261964806 ),
(2, 1, 'Company profile ', 14, 17, 50,126 4586212 ),
(3, 1, 'News', 12, 13, 50,126 4586226 ),
(4, 2, 'Company product', 10, 11, 50,126 4586249 ),
(5, 1, 'honorary qualification ', 8, 9, 50,126 4586270 ),
(6, 3, 'Data downloads', 6, 7, 50,126 4586295 ),
(7, 1, 'talent recruiters ', 4, 5, 50,126 4586314 ),
(8, 1, 'guest', 2, 3, 50,126 4586884 ),
(9, 1, 'President', 15, 16, 50,126 7771951 );

/**
* Display tree: displays all nodes.
* 1. Obtain the left and right values of the root node (the title of the root node is "top-level directory" by default ").
* 2. query records whose left and right values are within the left and right value ranges of the root node, and sort the records by the Left value.
* 3. If the right value of the current record is greater than the right value of the previous record, it is a subcategory and a space is added to the output.
* @ Return array
**/
Function display_tree (){

// Obtain the left and right values of the root user
$ Arr_lr = $ this-> category-> where ("title = 'top topic '")-> find ();
// Print_r ($ arr_lr );
If ($ arr_lr ){
$ Right = array ();
$ Arr_tree = $ this-> category-> query ("SELECT id, type, title, rgt FROM category WHERE lft> = ". $ arr_lr ['lft ']. "AND lft <= ". $ arr_lr ['rgt ']. "order by lft ");
Foreach ($ arr_tree as $ v ){
If (count ($ right )){
While ($ right [count ($ right)-1] <$ v ['rgt ']) {
Array_pop ($ right );
}
}
$ Title = $ v ['title'];
If (count ($ right )){
$ Title = '|-'. $ title;
}
$ Arr_list [] = array ('id' => $ v ['id'], 'type' => $ type, 'title' => str_repeat ('', count ($ right )). $ title, 'name' => $ v ['title']);
$ Right [] = $ v ['rgt '];
}
Return $ arr_list;
}
}

All right, as long as all the categories can be queried at one time, instead of recursion.
The following question is how to insert, delete, and modify an object.
Insert: The insert operation is simple to find its parent node. Then, add 2 to the left and right values of the nodes with the left and right values greater than the left values of the parent node, and then insert the current node, the left and right values are the parent node's left values plus one and two respectively. You can use a stored procedure to perform the operation:
Copy the Code as follows:
Create procedure 'category _ insert_by_parent '(IN pid INT, IN title VARCHAR (20), IN type INT, IN l_order INT, IN pubtime INT)
BEGIN
DECLARE myLeft INT;
SELECT lft into myLeft FROM category WHERE id = pid;
UPDATE qy_category SET rgt = rgt + 2 WHERE rgt> myLeft;
UPDATE qy_category SET lft = lft + 2 WHERE lft> myLeft;
Insert into qy_category (type, title, lft, rgt, lorder, create_time) VALUES (type, title, myLeft + 1, myLeft + 2, l_order, pubtime );
Commit;
END

Delete operation:
Principle of deletion: 1. Get the left and right values of the nodes to be deleted, and get their difference plus one, @ mywidth = @ rgt-@ lft + 1;
2. Delete the nodes between the current node and the left and right values.
3. Set the condition to all nodes whose values are greater than the right of the current node. The operation is to subtract the left and right values from @ mywidth.
The stored procedure is as follows:
Copy the Code as follows:
Create procedure 'category _ delete_by_key '(IN id INT)
BEGIN
SELECT @ myLeft: = lft, @ myRight: = rgt, @ myWidth: = rgt-lft + 1
FROM category
WHERE id = id;
Delete from category WHERE lft BETWEEN @ myLeft AND @ myRight;
UPDATE nested_category SET rgt = rgt-@ myWidth WHERE rgt> @ myRight;
UPDATE nested_category SET lft = lft-@ myWidth WHERE lft> @ myRight;

Modify:
I haven't seen any rule for a long time in the fatal modification operation. As long as this rule is made, delete it first and then insert it. Just call the above two stored procedures!
Summary: The query is convenient, but the addition, deletion, modification, and modification operations are a little complicated. However, there are not many such operations by category, but they are still used for query. It is also convenient to create a storage process!