Multi-SQL Query

[kool_samule]

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/articles/hierarchical-data.html

 

But when trying to run a query, such as:

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

[AbraCadaver]

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/articles/mysql-storedproc.html