当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - MySQL Nested Sets adding node above selected node

问题描述:

I have been trying to figure this out for a while, I have a MySQL query to add a node into the list, example:

Heren

-Pakken

-Schoenen

Dames

I'd like to add 'Children' before male. In this case, someone would say: Add Children before Male.

This is the query for adding Children after Male.

LOCK TABLE categories WRITE;

SELECT @myRight := rght FROM categories WHERE name = 'Male';

UPDATE categories SET rght = rght + 2 WHERE rght > @myRight;

UPDATE categories SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO categories(name, lft, rght) VALUES('Children',

@myRight + 1, @myRight + 2);

UNLOCK TABLES;

So, from: http://cl.ly/image/1U3O2k1F2I1T to: http://cl.ly/image/1D0k1Z1D2c18

Thanks :)

网友答案:

Figured it out:

LOCK TABLE `categories` WRITE;

SELECT @myLeft := `lft` FROM `categories`
WHERE `id` = '. $where .';

UPDATE `categories` SET `rght` = `rght` + 2 WHERE `rght` > @myLeft;
UPDATE `categories` SET `lft` = `lft` + 2 WHERE `lft` > @myLeft OR `lft` = @myLeft;

INSERT INTO `categories`(`name`, `lft`, `rght`) VALUES("'. $name .'", @myLeft, @myLeft + 1);

UNLOCK TABLES;
分享给朋友:
您可能感兴趣的文章:
随机阅读: