Implementing Nested Order Set in MySQL/PHP

Question

I'm trying to create a database in which there could be n number of categories as well as their sub-categories.

First I tried to create adjacency model database like this

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | Electronics          |   NULL |
|           2 | Mobile               |      1 |
|           3 | Washing Machine      |      1 |
|           4 | Samsung              |      2 |
+-------------+----------------------+--------+

but, I was facing a problem when deleting a node, like how to manage the child nodes for deleted nodes etc.

then I'm trying to implement Nested Order Set by Joe Celko

Table Structures in each Figure:

Figure 1:
+----+-------------+-----+-----+
| id | name        | lft | rgt |
+----+-------------+-----+-----+
| 1  | Electronics | 1   | 2   |
+----+-------------+-----+-----+

Figure 2:
+----+-------------+-----+-----+
| id | name        | lft | rgt |
+----+-------------+-----+-----+
| 1  | Electronics | 1   | 4   |
+----+-------------+-----+-----+
| 2  | Mobile      | 2   | 3   |
+----+-------------+-----+-----+

Figure 3:
+----+-----------------+-----+-----+
| id | name            | lft | rgt |
+----+-----------------+-----+-----+
| 1  | Electronics     | 1   | 6   |
+----+-----------------+-----+-----+
| 2  | Mobile          | 2   | 3   |
+----+-----------------+-----+-----+
| 3  | Washing Machine | 4   | 5   |
+----+-----------------+-----+-----+

Figure 4:
+----+-----------------+-----+-----+
| id | name            | lft | rgt |
+----+-----------------+-----+-----+
| 1  | Electronics     | 1   | 8   |
+----+-----------------+-----+-----+
| 2  | Mobile          | 2   | 5   |
+----+-----------------+-----+-----+
| 3  | Washing Machine | 6   | 7   |
+----+-----------------+-----+-----+
| 4  | Samsung         | 3   | 4   |
+----+-----------------+-----+-----+

but I'm unable to insert new node with correct rgt and lft. I'm using this but it is not generating the right values of rgt and lft.

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'Mobile';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('LG', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

Answer 1

This http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ is your source code I think ?

You're not using the good query, this one is the add a brother node.

You're after the add a child node :

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category
WHERE name = 'Mobile';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES('LG', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;