The left and right values to implement PHP infinite classification example

One, DB SQL statements

DB Used for PHP infinite classification
CREATE TABLE Tree (
ID Int (TEN) NOT null primary key auto_increment,
Name varchar (255) NOT NULL,
LFT Int (a) not null default 0,
RGT Int (a) not null default 0,
status int (1) NOT null default 0,
Index LFT (' LfT '),
Index RGT (' RGT '),
Index status (' status ')
) CharSet UTF8;

Insert into the tree value (null, ' Food ', 1,18,0);
Insert into the tree value (null, ' Fruit ', 2,11,0);
Insert into the tree value (null, ' Red ', 3,6,0);
Insert into the tree value (null, ' Cherry ', 4,5,0);
Insert into the tree value (null, ' Yellow ', 7,10,0);
Insert into the tree value (null, ' Banana ', 8,9,0);
Insert into the tree value (null, ' meat ', 12,17,0);
Insert into the tree value (null, ' Beef ', 13,14,0);
Insert into the tree value (null, ' pork ', 15,16,0);

Second, PHP file

<?php

error_reporting (0);
/*
1 Food 18
+------------------------------+
2 Fruit Meat 17
+-------------+                 +------------+
3 Red 6 7 yellow Beef Pork 16
4 Cherry 5 8 Banana 9

Descendants = (right–left-1)/2
*/

/**
* Used to move a node (including child nodes)
* @param array $pdata = array (' ID ' => primary key, ' root ' => name) two Select a parent node (insert the largest parent node for space)
* @param array $ndata = array (' ID ' => primary key, ' root ' => name) two Select a sibling node (not when you don't have a brother)
* @param array $cdata = array (' ID ' => primary key, ' root ' => name) two select a node that is currently being moved
*/
function Move_tree_all ($pdata =array (), $ndata =array (), $cdata =array ()) {
$cid = $cdata [' id ']? Intval ($cdata [' ID ']): ';
$croot = $cdata [' Root '];
if (! $cid &&! $croot) return;

Need to judge by self
1. CDATA cannot be the top
2, CDATA can not be higher than the $pdata grade

$adata = Get_tree_all ($cdata); Get all nodes of the current mobile node
Delete_tree_all ($cdata, 1); Logically deletes all nodes of the current mobile node

    foreach ($adata as $k => $val) {
        if ($k!= 0) {
            $pdata = array (' root ' => $val [' parent ']);
            insert_tree ($pdata, ', $val [' name '],1];
       } else {//first
             Insert_tree ($pdata, $ndata, $val [' name '],1];
       }
   }
}

/**
* Used to move one node (excluding child nodes)
* @param array $pdata = array (' ID ' => primary key, ' root ' => name) two Select a parent node (insert the largest parent node for space)
* @param array $ndata = array (' ID ' => primary key, ' root ' => name) two Select a sibling node (not when you don't have a brother)
* @param array $cdata = array (' ID ' => primary key, ' root ' => name) two select a node that is currently being moved
*/
function Move_tree_item ($pdata =array (), $ndata =array (), $cdata =array ()) {
$cid = $cdata [' id ']? Intval ($cdata [' ID ']): ';
$croot = $cdata [' Root '];
if (! $cid &&! $croot) return;

Need to judge by self
1. CDATA cannot be the top

if (! $croot) {
$sql = "Select name from tree where id = $cid";
$result = mysql_query ($sql);
$row = Mysql_fetch_assoc ($result);
$croot = $row [' name '];
Unset ($sql);
}

Delete_tree_item ($cdata, 1);
Insert_tree ($pdata, $ndata, $croot, 1);
}

/**
* Used to insert a node
* @param array $pdata = array (' ID ' => primary key, ' root ' => name) two Select a parent node (insert the largest parent node for space)
* @param array $ndata = array (' ID ' => primary key, ' root ' => name) two Select a sibling node (not when you don't have a brother)
* @param string $name string New insert Name
* @param int $update null by default, insert for 1 o'clock
*/
function Insert_tree ($pdata =array (), $ndata =array (), $name, $update = ') {
if (! $name) return;

$pid = $pdata [' id ']? Intval ($pdata [' ID ']): ';
$proot = $pdata [' Root '];

$nid = $ndata [' id ']? Intval ($ndata [' ID ']): ';
$nroot = $ndata [' Root '];

The father has no brother (the smallest child node, the parent node's last son)
if ($pid | | $proot) &&! ( $nid | | $nroot)) {
$sql = $pid? "Select LfT, RGT from the Where id = ' {$pid} ';": "Select LfT, RGT from tree where name = ' {$proot} ';";
$result = mysql_query ($sql);
$row = Mysql_fetch_assoc ($result);
Unset ($sql);

New node


$lft = $row [' RGT '];


$RGT = $lft +1;


if (! $update) {


$sql = "INSERT into tree values (null, ' {$name} ', $lft, $RGT, 0);";


$sql 1 = "Update tree set RGT = rgt+2 where RGT &gt;= {$row [' RGT ']}";


$sql 2 = "Update tree set lft = lft+2 where lft &gt;= {$row [' RGT ']}";


} else {


$sql = "Update tree set lft= $lft, rgt= $rgt, status=0 where name = ' {$name} ';";


$sql 1 = "Update tree set RGT = rgt+2 where status =0 and RGT &gt;= {$row [' RGT ']}";


$sql 2 = "Update tree set lft = lft+2 where status =0 and LfT &gt;= {$row [' RGT ']}";


}





mysql_query ($sql 1);


mysql_query ($sql 2);


mysql_query ($sql); Last Add new data


}

A father and a brother
if ($pid | | $proot) && ($nid | | | $nroot)) {
$sql = $nid? "Select LfT, RGT from the Where id = ' {$nid} ';": "Select LfT, RGT from tree where name = ' {$nroot} ';";
$result = mysql_query ($sql);
$row = Mysql_fetch_assoc ($result);
Unset ($sql);

New node


$lft = $row [' LfT '];


$RGT = $lft +1;


if (! $update) {


$sql = "INSERT into tree values (null, ' {$name} ', $lft, $RGT, 0);";


$sql 1 = "Update tree set RGT = rgt+2 where RGT &gt;= {$row [' lft ']};";


$sql 2 = "Update tree set lft = lft+2 where lft &gt;= {$row [' lft ']};";


} else {


$sql = "Update tree set lft= $lft, rgt= $rgt, status=0 where name = ' {$name} ';";


$sql 1 = "Update tree set RGT = rgt+2 where status = 0 and RGT &gt;= {$row [' lft ']};";


$sql 2 = "Update tree set lft = lft+2 where status = 0 and LfT &gt;= {$row [' lft ']};";


}


mysql_query ($sql 1);


mysql_query ($sql 2);


mysql_query ($sql); Last Add new data


}

No father, no brother (big guy)
if (!) ( $pid | | $proot) &&! ($nid | | $nroot)) {
$sql = "Select Max (' RGT ') as RGT from tree;";
$result = mysql_query ($sql);
$row = Mysql_fetch_assoc ($result);
Unset ($sql);

New node


$lft = 1;


$RGT = $row [' RGT ']+2;


if (! $update) {


$sql = "INSERT into tree values (null, ' {$name} ', $lft, $RGT, 0);";


$sql 1 = "Update tree set RGT = rgt+1";


$sql 2 = "Update tree set lft = lft+1";


} else {


$sql = "Update tree set lft= $lft, rgt= $rgt, status=0 where name = ' {$name} ';";


$sql 1 = "Update tree set RGT = rgt+1 where status = 0";


$sql 2 = "Update tree set lft = lft+1 where status = 0";


}





mysql_query ($sql 1);


mysql_query ($sql 2);


mysql_query ($sql); Last Add new data


}





}

/**
* Used to delete a node (including child nodes)
* @param array $data = array (' ID ' => primary key, ' root ' => name) two Select a
* @param int $update null by default, tombstone for 1 o'clock
*/
function Delete_tree_all ($data, $update = ' ") {
$id = $data [' id ']? Intval ($data [' ID ']): ';
$root = $data [' Root '];
if (! $id &&! $root) return;

$sql = $id? "Select LfT, RGT from the Where id = ' {$id} ';": "Select LfT, RGT from tree where name = ' {$root} ';";
$result = mysql_query ($sql);
$row = Mysql_fetch_assoc ($result);
Unset ($sql);

$middle = $row [' RGT ']-$row [' LfT ']+1;


if (! $update) {


$sql = "Delete from the tree where LfT BETWEEN '". $row [' LfT ']. "' and '". $row [' RGT ']. "'";


$sql 1 = "Update tree set RGT = rgt-{$middle} where RGT &gt; {$row [' RGT ']}";


$sql 2 = "Update tree set lft = lft-{$middle} where LfT &gt; {$row [' RGT ']}";


} else {


$sql = "Update tree set status = 1 where LfT BETWEEN '". $row [' LfT ']. "' and '". $row [' RGT ']. "'";


$sql 1 = "Update tree set RGT = rgt-{$middle} where status=0 and Rgt &gt; {$row [' RGT ']}";


$sql 2 = "Update tree set lft = lft-{$middle} where status=0 and LfT &gt; {$row [' RGT ']}";


}





mysql_query ($sql);


mysql_query ($sql 1);


mysql_query ($sql 2);


}

/**
* Used to delete a node (excluding child nodes)
* @param array $data = array (' ID ' => primary key, ' root ' => name) two Select a
* @param int $update null by default, tombstone for 1 o'clock
*/
function Delete_tree_item ($data, $update = ' ") {
$id = $data [' id ']? Intval ($data [' ID ']): ';
$root = $data [' Root '];
if (! $id &&! $root) return;

$sql = $id? "Select Id,lft, RGT from the Where id = ' {$id} ';": "Select Id,lft, RGT from tree where name = ' {$root} ';";
$result = mysql_query ($sql);
$row = Mysql_fetch_assoc ($result);
Unset ($sql);

if (! $update) {


$sql = "Delete from tree where id = {$row [' id ']};";


$sql 1 = "Update tree set RGT = Rgt-1,lft = lft-1 where lft &gt; {$row [' LfT ']} and RGT &lt; {$row [' RGT ']}";


$sql 2 = "Update tree set lft = lft-2 where lft &gt; {$row [' RGT ']}";


$sql 3 = "Update tree set RGT = rgt-2 where RGT &gt; {$row [' RGT ']}";


} else {


$sql = "Update tree set status = 1 WHERE id = {$row [' id ']};";


$sql 1 = "Update tree set RGT = Rgt-1,lft = lft-1 where status = 0 and LfT &gt; {$row [' LfT ']} and RGT &lt; {$row [' RGT ']}";


$sql 2 = "Update tree set lft = lft-2 where status = 0 and LfT &gt; {$row [' RGT ']}";


$sql 3 = "Update tree set RGT = rgt-2 where status = 0 and RGT &gt; {$row [' RGT ']}";


}





mysql_query ($sql);


mysql_query ($sql 1);


Can do or don't do just right,but does load empty 2 number in middle


mysql_query ($sql 2);


mysql_query ($sql 3);


}

/**
* Used to get all the nodes
* @param array $data = array (' ID ' => primary key, ' root ' => name) two Select a
*/
function Get_tree_all ($data) {
$id = $data [' id ']? Intval ($data [' ID ']): ';
$root = $data [' Root '];
if (! $id &&! $root) return;

$sql = $id? "Select LfT, RGT from the Where id = ' {$id} ';": "Select LfT, RGT from tree where name = ' {$root} ';";
$result = mysql_query ($sql);
$row = Mysql_fetch_assoc ($result);

    $adata   = array ()//All data
    $right   = array ();//Count
     $prev    = Array ();
    $result = mysql_query ("Select Id,name, LfT, rgt from tree WHERE lft BETWEEN".) $row [' LfT ']. "' and '". $row [' RGT ']. "' Order by LfT ASC; ");
    while ($row = Mysql_fetch_assoc ($result)) {
        if ( Count ($right) > 0) {
            while ($right [count ($ right)-1] < $row [' RGT ']) {//Check whether we should move the node out of the stack
                 Array_pop ($right);
                Array_pop ($prev) ;
           }
       }

$parent = $prev? End ($prev): ';
$adata [] = array (' ID ' => $row [' id '], ' name ' => $row [' name '], ' Level ' =>count ($right), ' parent ' => $parent);

$right [] = $row [' RGT '];
$prev [] = $row [' name '];
}
return $adata;
}

/**
* For display classification
* @param array $data = array (' ID ' => primary key, ' root ' => name) two Select a
*/
function Display_tree ($data) {
$id = $data [' id ']? Intval ($data [' ID ']): ';
$root = $data [' Root '];
if (! $id &&! $root) return;

    $sql     =  $id? "Select LfT, RGT from the Where id = ' {$id} ';": "Select LfT, RGT from tree where name = ' {$root} ';";
    $result = mysql_query ($sql);
    $row     = Mysql_fetch_assoc ($result);

    $right   = Array ();
    $result = mysql_query ("Select name, LfT, RGT from tree WHERE lft BETWEEN '".) $row [' LfT ']. "' and '". $row [' RGT ']. "' Order by LfT ASC; ");
    while ($row = Mysql_fetch_assoc ($result)) {
        if ( Count ($right) > 0) {//Check if we should move the node out of the stack
            while ($right [count ($right)-1] < $row [' RGT ']) {
                 Array_pop ($right);
           }
       }
        Echo str_repeat ('   ', Count ($right)). $row [' name ']. "\ n";
        $right [] = $row [' RGT '];
   }
}

mysql_connect (' localhost ', ' root ', ') ' or Die (' Connect error ');
mysql_select_db (' test ') or Die (' Database error ');
mysql_query (' Set names UTF8 ');

Display_tree (Array (' root ' => ' Food '));
Display_tree (Array (' root ' => ' Bigboss '));

Move_tree_all ($pdata =array (' root ' => ' Fruit '), $ndata =array (' root ' => ' Red '), $cdata =array (' root ' => ' meat ') ));
Move_tree_all (', ', $cdata =array (' root ' => ' meat '));
Move_tree_item (', ', ', array (' Root ' => ' Red '));
Move_tree_item (Array (' root ' => ' Red '), array (' Root ' => ' Cherry '), array (' Root ' => ' Fruit '));

Delete_tree_all (Array (' root ' => ' yellow '));
Delete_tree_all (Array (' root ' => ' meat '));
Delete_tree_item (Array (' root ' => ' meat '));

Insert_tree (', ', ', ' Bigboss ');
Insert_tree (Array (' root ' => ' Red '), ', ' Dalao ');
Insert_tree (Array (' root ' => ' Red '), array (' Root ' => ' Cherry '), ' Baddalao ');
Insert_tree (Array (' root ' => ' Fruit '), array (' Root ' => ' Red '), ' redbother ');

Display_tree (Array (' root ' => ' Food '));