PHP scripts with sql comands that have @ in them

[timgerr]

Hello all,
I have a question for ya. I am trying to do this tree structure from

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

and the query has @ symbols in them. I cannot get the queries to work because of the @ symbols. What can I do to get the queries to work?

Here is an excerpt of the code:

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;

We can then check our nesting with our indented tree query:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!

 

[jpadie]

that sql looks fine to me. the @sign means a variable and the := is the assignment operator.

are you perhaps using a very old version of mysql? variables have only been supported since 3.23.6

in the alternative, can you post the errors you are getting. here if the errors are php related and in the mysql forum if they are purely mysql issues.

 

[DonQuichote]

I do not see anything wrong with the above code. SQL variables are a great way to carry over data. For instance, It allows you to keep a last inserted ID and use it for more than one related record.

It is good to know the lifetime of these variables: they only exist as long as the connection exists. So if you cut the above SQL file in separate queries and use a separate connection for each query, it wont work, in the sense that the variable will have a default value of NULL.

You could try to feed the SQL file to the command-line client if you do not already do so:

mysql -u [i]YourUserName[/i] -p [i]DatabaseName[/i] < [i]TheSqlFile[/i]

I cannot get the queries to work because of the @ symbols.

What do you mean? What happens? Is there an error message?


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)