PHP MySQL Unlimited class implementation principle and code

Table structure:
Copy code code as follows:
--
--The structure of the table ' category '
--
CREATE TABLE IF not EXISTS ' category ' (
' id ' int (one) not NULL auto_increment,
' Type ' int (one) not NULL COMMENT ' 1 for Article Type 2 for product Type 3 for download type ',
' title ' varchar Not NULL,
' LfT ' int (one) is not NULL,
' RGT ' int (one) is not NULL,
' Lorder ' int (one) not NULL COMMENT ' sort ',
' Create_time ' int (one) is not NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8 auto_increment=10;
--
--Export the data in the table ' category '
--
INSERT into ' category ' (' id ', ' type ', ' title ', ' LfT ', ' rgt ', ' lorder ', ' create_time ') VALUES
(1, 1, ' Top columns ', 1, 18, 1, 1261964806),
(2, 1, ' Company Profile ', 14, 17, 50, 1264586212),
(3, 1, ' News ', 12, 13, 50, 1264586226),
(4, 2, ' Company Products ', 10, 11, 50, 1264586249),
(5, 1, ' honor credentials ', 8, 9, 50, 1264586270),
(6, 3, ' data downloads ', 6, 7, 50, 1264586295),
(7, 1, ' talent recruitment ', 4, 5, 50, 1264586314),
(8, 1, ' Message board ', 2, 3, 50, 1264586884),
(9, 1, ' president ', 15, 16, 50, 1267771951);

/**
* Show the tree and show all the nodes.
* 1, first get the root node of the left and right values (the default root of the title is the "top-level directory").
* 2, query the left and right values in the root node of the value of the range of records, and sorted according to the left value.
* 3, if the right value of this record is greater than the previous record of the right value is a subcategory, the output time with spaces.
* @return Array
**/
function Display_tree () {

//Get the value on the left and right of root
$arr _LR = $this->category->where ("title = ' top Column '")->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_po P ($right);
}
}
$title = $v [' title '];
if (count ($right)) {
$title = ' | | '. $title;
}
$arr _list[] = array (' ID ' => $v [' id '], ' type ' => $type, ' title ' => str_repeat (', Count ($right)). $tit Le, ' name ' => $v [' title ']];
$right [] = $v [' RGT '];
}
return $arr _list;
}
}

All right, so long as all the categories can be queried at once, instead of passing recursively.
The following question is how to insert, delete, and modify actions
Insert: The insert operation is very simple to find its parent node, and then the left and right values are greater than the left values of the nodes of the node plus 2, and then insert this node, the left value of the parent node plus one and plus two, you can use a stored procedure to operate:
Copy code code as follows:
CREATE PROCEDURE ' category_insert_by_parent ' (in PID int,in title VARCHAR, 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_ord Er, pubtime);
Commit;
End

Delete action:
The principle of deletion: 1. Get the left and right values of the nodes and get their difference plus one, @mywidth = @rgt-@lft + 1;
2. Delete the left and right values between nodes in this node
3. Modify all nodes whose condition is greater than the right value of this node, and manipulate them to subtract @mywidth
The stored procedure is as follows:
Copy code 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:
Fatal modification operation, I saw a long time also did not see what the law out, as long as this unwise, first delete and then insert, as long as the call above 2 stored procedures on it!
Summary: Convenient query, but the modification of the operation is a bit cumbersome, but the general classification of such operations is not a lot, or query used more, and then get a stored procedure is also convenient!