I have a perfectly working MySQL query written below. But when I place it in the mysql_query() fucntion to query the data, I receive the following error. If I remove the SET command then all works fine. Any ideas what I can do to use the SET function in mysql_query() without breaking it? Thanks!
Query:
SET @rank=0;
SELECT rank FROM (
SELECT @rank:=@rank+1 AS rank, value, name, parent_id
FROM core_actionfield
where name = 'pacman_score'
ORDER BY CAST(value as Signed) DESC
) as result
WHERE parent_id = 123456 ";
Error when I place it in mysql_query():
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT rank FROM (SELECT @rank:=@rank+1 AS rank, value, name, parent_id FROM cor' at line 1
MySQL SET @ not working in PHP mysql_query()
Moderator: General Moderators
-
seagle2010
- Forum Newbie
- Posts: 3
- Joined: Mon Oct 11, 2010 7:56 pm
Re: MySQL SET @ not working in PHP mysql_query()
those are two queries (SET and SELECT) mysql_query() doesn't support multi-queries ... you can do this:seagle2010 wrote: SET @rank=0;
SELECT rank FROM (
SELECT @rank:=@rank+1 AS rank, value, name, parent_id
FROM core_actionfield
where name = 'pacman_score'
ORDER BY CAST(value as Signed) DESC
) as result
WHERE parent_id = 123456 ";
Code: Select all
$query = "SET @rank=0";
mysql_query($query) or die(mysql_error());
$query = "SELECT rank
FROM (SELECT @rank:=@rank+1 AS rank, value, name, parent_id
FROM core_actionfield
WHERE name = 'pacman_score'
ORDER BY CAST(value as Signed) DESC) as result
WHERE parent_id = 123456";
$rs = mysql_query($query) or die(mysql_error());-
seagle2010
- Forum Newbie
- Posts: 3
- Joined: Mon Oct 11, 2010 7:56 pm
Re: MySQL SET @ not working in PHP mysql_query()
Second query doesn't appear to be picking up the SET @rank value. Is there a way to add SET to the other query or do they have to remain as two?
Thanks! I'm starting to explore the MYSQLli extension.
Thanks! I'm starting to explore the MYSQLli extension.
Re: MySQL SET @ not working in PHP mysql_query()
are you sure?... I did test that code and works for me without problemseagle2010 wrote:Second query doesn't appear to be picking up the SET @rank valu
no as far as I know.seagle2010 wrote:Is there a way to add SET to the other query or do they have to remain as two?
-
seagle2010
- Forum Newbie
- Posts: 3
- Joined: Mon Oct 11, 2010 7:56 pm
Re: MySQL SET @ not working in PHP mysql_query()
You are correct, it does work. Thanks again for the help.