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 fil开发者_C百科e encoding problem? Does the query() function support multiple queries at once? I am stumped.

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.

I don't know about codeigniter, but when I do those queries in php, you can use a "mysqli" object and perform multiple queries using the mysqli_multi_query() function.

Personally, I wrote a little function for my own use:

// $queries should be an array of strings (individual queries).
private function multi_query($queries) {
    foreach ($queries as $i=>$q) {
        $results[] = mysql_query($q, $this->con);
    }
    return $results;
}

It could easily be written to prepend a "BEGIN" query and append a "COMMIT" query to the array (if you're using MySQL 5.0 + ) which will make sure all the queries are run sequentially.