Page 1 of 1

MySQL SET @ not working in PHP mysql_query()

Posted: Mon Oct 11, 2010 8:02 pm
by seagle2010
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

Re: MySQL SET @ not working in PHP mysql_query()

Posted: Mon Oct 11, 2010 8:47 pm
by mikosiko
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 ";
those are two queries (SET and SELECT) mysql_query() doesn't support multi-queries ... you can do this:

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());
as an alternative you can use mysqli that allows multi-queries

Re: MySQL SET @ not working in PHP mysql_query()

Posted: Mon Oct 11, 2010 9:14 pm
by seagle2010
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.

Re: MySQL SET @ not working in PHP mysql_query()

Posted: Mon Oct 11, 2010 9:23 pm
by mikosiko
seagle2010 wrote:Second query doesn't appear to be picking up the SET @rank valu
are you sure?... I did test that code and works for me without problem
seagle2010 wrote:Is there a way to add SET to the other query or do they have to remain as two?
no as far as I know.

Re: MySQL SET @ not working in PHP mysql_query()

Posted: Tue Oct 12, 2010 12:00 pm
by seagle2010
You are correct, it does work. Thanks again for the help.