Page 1 of 1

How would you perform this query with php?

Posted: Fri Apr 03, 2009 7:16 am
by thinsoldier
How would you perform this query with php? 
or is it not possible

Code: Select all

LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES;
 
It seems after each ; php dies with a mysql error. Like it only allows one part of the query per mysql_query() call.

So if the part where I create @myRight is in a seperate mysql_query call from the 2 UPDATE parts,
will @myRight even be carried over in the 2 subsequent calls? Doesn't seem so.

Re: How would you perform this query with php?

Posted: Sat Apr 04, 2009 4:21 am
by greyhoundcode
Have you tried the query from within phpMyAdmin?

Re: How would you perform this query with php?

Posted: Mon Apr 06, 2009 1:33 pm
by thinsoldier
greyhoundcode wrote:Have you tried the query from within phpMyAdmin?
Yeah, it works there. But I need it to work from PHP since this will be part of my administration area. I have lots of 1-to-many and many-to-many relationships in my data so every edit to a record mean lots of records in multiple tables get modified as one process.

Re: How would you perform this query with php?

Posted: Mon Apr 06, 2009 1:40 pm
by John Cartwright
mysql_query() only supports one query at a time, so break those up into different queries, and you'll be ok.