Page 1 of 1

Multi-SQL Query

Posted: Tue Feb 01, 2011 9:14 am
by koolsamule
Hi Chaps,

I'm trying to put together a Nested Set Model (Hierarchical Data), but I have having problems when it comes to deleting/updating the 'nested' table.

I'm using this as a guide:
http://dev.mysql.com/tech-resources/art ... l-data.htm

But when trying to run a query, such as:

Code: Select all

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;
I get a MySQL syntax error.

This code works fine in something such as Windows Command Prompt, but not in PHP.

Is there a special way to run multiple queries in 1 PHP script?

Many thanks

Re: Multi-SQL Query

Posted: Tue Feb 01, 2011 10:10 am
by AbraCadaver
Deja Vu :D

If you use mysqli extension instead there is: http://us2.php.net/manual/en/mysqli.multi-query.php

However I'm not sure if the vars defined in the first query will pass to the others. If not you can probably write a stored procedure: http://dev.mysql.com/tech-resources/art ... dproc.html