Page 1 of 1

MySQL query works in phpMyAdmin but not when using functions

Posted: Sun Apr 13, 2008 3:17 pm
by impulse()
Hi,

I have the following query to run:

Code: Select all

SET @pos=0;
SELECT @pos:=@pos+1 as pos, MAX(score) as highscore, mid
FROM games_scores
WHERE gid = 10
GROUP BY mid
ORDER BY highscore DESC
And that works fine when I run it in phpMyAdmin but if I use the following PHP code:

Code: Select all

mysql_connect("x","x","x");
mysql_select_db("x");
 
$q = mysql_query("SET @pos=0;
SELECT @pos:=@pos+1 as pos, MAX(score) as highscore, mid
FROM games_scores
WHERE gid = 10
GROUP BY mid
ORDER BY highscore DESC") or die(mysql_error());
 
 
while($r = mysql_fetch_array($q))
        echo $r['pos']."\n";
then I have this error returned:
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 @pos:=@pos+1 as pos, MAX(score) as highscore, mid FROM
The query is being run on the same server whether it's run in phpMyAdmin or a PHP script. And I have tried a simple SELECT * FROM <table> query and runs finde in the same script.

Has anybody encountered this problem before or knows where the problem is?

Re: MySQL query works in phpMyAdmin but not when using functions

Posted: Sun Apr 13, 2008 5:06 pm
by EverLearning
As written in PHP manual for mysql_query
mysql_query() sends an unique query (multiple queries are not supported)
So you'll need 2 calls to mysql_query:

Code: Select all

$query1 = mysql_query("SET @pos=0");
$query2 = mysql_query("SELECT @pos:=@pos+1 as pos, MAX(score) as highscore, mid
FROM games_scores
WHERE gid = 10
GROUP BY mid
ORDER BY highscore DESC") or die(mysql_error());
 
while($r = mysql_fetch_array($query2))
        echo $r['pos']."\n";

Re: MySQL query works in phpMyAdmin but not when using functions

Posted: Mon Apr 14, 2008 2:09 am
by impulse()
Thank you very much.