初始结构
Food初始表
CREATE TABLE IF NOT EXISTS `food` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL,
`lft` int(100) DEFAULT NULL,
`rgt` int(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- 导出表中的数据 `food`
--
INSERT INTO `food` (`id`, `name`, `lft`, `rgt`) VALUES
(0, 'Food', 1, 18),
(1, 'Fruit', 2, 11),
(3, 'Red', 3, 6),
(4, 'Cherry', 4, 5),
(5, 'Yellow', 7, 10),
(6, 'Banana', 8, 9),
(7, 'Meat', 12, 17),
(8, 'Beef', 13, 14),
(9, 'Pork', 15, 16);
将加节点写成存储过程，从安全角度、复用性、优化性得到提高 。
================================================================加节点 CALL addjiedian('Red','Strawberry');
DELIMITER //
CREATE PROCEDURE addjiedian(IN dangqianjiedianname VARCHAR(10),IN zijidianname VARCHAR(10))
begin
SELECT @myLeft:=lft,@myRight := rgt FROM Food
WHERE name = dangqianjiedianname;
UPDATE Food SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE Food SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO Food(name, lft, rgt) VALUES(zijidianname, @myLeft + 1, @myLeft +2);
END
//
DELIMITER ;
================================================================删除节点及子节点 CALL deleteallzijiedian(4); id号
DELIMITER //
CREATE PROCEDURE deleteallzijiedian(IN pid int)
begin
select @myLeft:=lft, @myRight:=rgt, @myWidth:=rgt-lft+1
from Food where id=pid;
delete from Food where lft between @myLeft and @myRight;
select * from Food;
update Food set rgt=rgt-@myWidth where rgt>@myRight;
update Food set lft=lft-@myWidth where lft>@myLeft;
select * from Food;
END
//
DELIMITER ;
删除节点 （节点没有子节点的节点） CALL deletezijiedian(6,7,'Strawberry');
待续。。。。
php取数据
http://www.jb51.net/article/52188.htm
其它参考资料
https://wenku.baidu.com/view/634656b0561252d381eb6e8f
http://blog.csdn.net/i_bruce/article/details/41558063