Page 1 of 1

What´s wrong in this SQL?

Posted: Sun Jan 24, 2010 11:36 am
by Pefo
Hi

Working on hierachies in MySQL.
This is my SQL for adding a child.

Code: Select all

"LOCK TABLE meny WRITE;
    
SELECT @myLeft := lft FROM meny
WHERE namn = 'ParentMenu';
 
UPDATE meny SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE meny SET lft = lft + 2 WHERE lft > @myLeft;
 
INSERT INTO meny(namn, lft, rgt) VALUES('ChildMenu', @myLeft + 1, @myLeft + 2);
 
UNLOCK TABLES;"

Running this SQL in my DB-client (Sequel-Pro) works perfect.
The exact same code in PHP on the same DB (local) gives the MySQLerror:

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 'SELECT @myLeft := lft FROM meny WHERE namn = 'ParentMenu'; UPDATE meny S' at line 3

Is it a charset issue, linebreak type? Trying to change the both but...

Re: What´s wrong in this SQL?

Posted: Sun Jan 24, 2010 2:47 pm
by Pefo
Of course!
Can see the code behind the letters now!

Re: What´s wrong in this SQL?

Posted: Sun Jan 24, 2010 3:16 pm
by Eran
How are you trying to run this query? if you are using mysql_query() you should note it cannot run multiple statements with one call. consider using mysqli or PDO, or use multiple calls

Re: What´s wrong in this SQL?

Posted: Mon Jan 25, 2010 3:42 pm
by Pefo
Yes, thanks. I realized that!
Got spoiled by my db-client doing that for me.

Works now, but my variable @myLeft seems to be lost between calls.
Will look into mysqli and transactions.

/Peter