Search code examples
mysqlcodeigniternested-sets

Nested Set SQL problem

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category

WHERE name = '2 WAY RADIOS';

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('FRS', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;

I tried running this sample code in codeigniter via $this->db->query() but I get this error

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOCK TABLE nested_category WRITE; SELECT @myLeft := lft FROM nested_category WHERE name = '2 WAY RADIOS';' at line 3

LOCK TABLE nested_category WRITE; SELECT @myLeft := lft FROM nested_category WHERE name = '2 WAY RADIOS'; 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('FRS', @myLeft + 1, @myLeft + 2); UNLOCK TABLES;

I am sure the database is not the problem since I ran this SQL in phpmyadmin and it worked. But when I run at via code, I get an error. Could this be a codeigniter problem? A file encoding problem? Does the query() function support multiple queries at once? I am stumped.

Solution

  • In fact it does not. Most user front-ends for MySQL will split multiple up SQL queries you input and send them one by one, so you might not have noticed that.

    You need to split each query into its own query() call, and it will work as expected assuming each query() is called on the same MySQL connection/session.

    On another note, if you're using MySQL 5.0 or above, you can use a transaction instead of having to manually lock/unlock your tables. Just replace LOCK TABLE nested_category WRITE with BEGIN and UNLOCK TABLES with COMMIT.