mysql issue on inserts Error Number: 1064

[eluser]veritascs[/eluser]
Hey all, I'm slightly confused here. I'm getting an SQL error when trying to insert data (categories/hierarchical data). However, when I use the SQL from the returned error, and paste in through command line it works fine. What am I missing?

When CI sends the insert it is returning 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 'UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE categories SET l' at line 4

SELECT @myLeft := lft FROM categories WHERE category_id = '2'; UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE categories SET lft = lft + 2 WHERE lft > @myLeft; INSERT INTO categories(category_name, category_url_name, category_description, lft, rgt) VALUES('sd', 'sd', 'sd', @myLeft + 1, @myLeft + 2);

code in my model:

Code:

//adding to a node that has existing children
$sql = "
LOCK TABLE categories WRITE;

SELECT @myLeft := lft FROM categories WHERE category_id = ?;

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

INSERT INTO categories(category_name, category_url_name, category_description, lft, rgt)
VALUES(?, ?, ?, @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;";

$query = $this->db->query($sql, array(
    $parent->category_id,
    $catName,
    $catURLName,
    $catDescription));

Let me know if you need any other code/info. Thanks

[eluser]Bart Mebane[/eluser]
You can try checking the value of $parent->category_id, and also look at $this->db->last_query().

[eluser]AlexJ[/eluser]
You can't execute multiple queries with the query() method I think, explode the query by ; delimiter and execute them one at a time

[eluser]veritascs[/eluser]
Thanks AlexJ! Working as expected now!