Multi-SQL Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Multi-SQL Query

Post 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
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Multi-SQL Query

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply