MYSQL Variables

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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

MYSQL Variables

Post by kendall »

Hi,

i came across an unusual select statement
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
what is the @myRight:=?

How is this used...? why is it used?

Now i got the syntax from a code that updates a record from the value of that variable select statement then uses that same variable to insert its value into a new record

what is it rreally?

Im not seeing this on the mysql docs

Kendall
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Exactly as you said: it are variables... More can be read here:
http://dev.mysql.com/doc/refman/5.0/en/variables.html
http://dev.mysql.com/doc/refman/5.0/en/set-option.html

eg: It allows you to let mysql save a couple of values instead of fetching them first into a php variable... And then pushing it back (via a query) to update something.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

MYSQL Variables

Post by kendall »

Well...
im trying to execute the following code

Code: Select all

LOCK TABLE cms_menu_test WRITE;
SET @myRight = 0;
SELECT @myRight:=GroupEnd FROM cms_menu_test WHERE MenuID = 10;
UPDATE cms_menu_test SET GroupEnd = GroupEnd + 2 WHERE GroupEnd > @myRight;
UPDATE cms_menu_test SET GroupStart = GroupStart + 2 WHERE GroupStart > @myRight;
INSERT INTO cms_menu_test (MenuID,MenuName,GroupStart,GroupEnd) VALUES('','Sub Level 3',@myRight+1,@myRight+2);
UNLOCK TABLES;
But i get a 'Cant be null' error in my insert statement

on checking the logs i see in my select statment i have
SELECT @myRightNULLGroupEnd FROM cms_menu_test WHERE MenuID = 10
It would mean that the myRight value from the select statement is not being set
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I can't reproduce the problem... (Everything seems to work as it should)

Code: Select all

mysql> show columns from weight;
+----------+--------------+------+-----+---------------------+-------+
| Field    | Type         | Null | Key | Default             | Extra |
+----------+--------------+------+-----+---------------------+-------+
| datetime | datetime     |      | PRI | 0000-00-00 00:00:00 |       |
| weight   | decimal(5,1) |      |     | 0.0                 |       |
+----------+--------------+------+-----+---------------------+-------+
2 rows in set (0.00 sec)

mysql> set @datetime = NOW();
Query OK, 0 rows affected (0.00 sec)

mysql> select datetime;
ERROR 1054: Unknown column 'datetime' in 'field list'
mysql> select @datetime;
+---------------------+
| @datetime           |
+---------------------+
| 2006-01-11 22:14:17 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @datetime := datetime from weight order by datetime desc limit 1;
+-----------------------+
| @datetime := datetime |
+-----------------------+
| 2005-07-11 13:24:31   |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @datetime;
+---------------------+
| @datetime           |
+---------------------+
| 2005-07-11 13:24:31 |
+---------------------+
1 row in set (0.01 sec)
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

Is this a PHP version 5 feature?
Post Reply