Page 1 of 1

MYSQL Variables

Posted: Wed Jan 11, 2006 7:41 am
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

Posted: Wed Jan 11, 2006 9:37 am
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.

MYSQL Variables

Posted: Wed Jan 11, 2006 10:14 am
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

Posted: Wed Jan 11, 2006 3:11 pm
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)

Posted: Thu Feb 23, 2006 3:40 pm
by kendall
Is this a PHP version 5 feature?