What´s wrong in this SQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Pefo
Forum Newbie
Posts: 9
Joined: Wed Dec 16, 2009 5:10 pm

What´s wrong in this SQL?

Post 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...
Pefo
Forum Newbie
Posts: 9
Joined: Wed Dec 16, 2009 5:10 pm

Re: What´s wrong in this SQL?

Post by Pefo »

Of course!
Can see the code behind the letters now!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: What´s wrong in this SQL?

Post 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
Pefo
Forum Newbie
Posts: 9
Joined: Wed Dec 16, 2009 5:10 pm

Re: What´s wrong in this SQL?

Post 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
Post Reply