MySQL query works in phpMyAdmin but not when using functions

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
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

MySQL query works in phpMyAdmin but not when using functions

Post 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?
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

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

Post 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";
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

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

Post by impulse() »

Thank you very much.
Post Reply