Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
kendall
Forum Regular
Posts: 852 Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:
Post
by kendall » Wed Jan 11, 2006 7:41 am
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
kendall
Forum Regular
Posts: 852 Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:
Post
by kendall » Wed Jan 11, 2006 10:14 am
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 » Wed Jan 11, 2006 3:11 pm
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)
kendall
Forum Regular
Posts: 852 Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:
Post
by kendall » Thu Feb 23, 2006 3:40 pm
Is this a PHP version 5 feature?