How would you perform this query with php?

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
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

How would you perform this query with php?

Post 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.
Warning: I have no idea what I'm talking about.
User avatar
greyhoundcode
Forum Regular
Posts: 613
Joined: Mon Feb 11, 2008 4:22 am

Re: How would you perform this query with php?

Post by greyhoundcode »

Have you tried the query from within phpMyAdmin?
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: How would you perform this query with php?

Post 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.
Warning: I have no idea what I'm talking about.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: How would you perform this query with php?

Post by John Cartwright »

mysql_query() only supports one query at a time, so break those up into different queries, and you'll be ok.
Post Reply