MySQL SET @ not working in PHP mysql_query()

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
seagle2010
Forum Newbie
Posts: 3
Joined: Mon Oct 11, 2010 7:56 pm

MySQL SET @ not working in PHP mysql_query()

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post 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
seagle2010
Forum Newbie
Posts: 3
Joined: Mon Oct 11, 2010 7:56 pm

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

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

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

Post 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.
seagle2010
Forum Newbie
Posts: 3
Joined: Mon Oct 11, 2010 7:56 pm

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

Post by seagle2010 »

You are correct, it does work. Thanks again for the help.
Post Reply