MySQL query wont work in PHP

[am024196]

Hi there, I'm pretty new to mySQL and wondered if anyone could help with a problem ive got?

I have a table that holds players scores for a game im trying to make,
what i want to do is return where a particular player is in that list ordered by high score.

I know that i could work this out with PHP - but i think that i would need to return the whole player scores table which given there could be a couple of thousand players of this thing (ever the optimist) seems rather drastic given that i just want one tiny number.

My thinking is that i first need to order the table into descending order to get the high scores at the top and low at the bottom. I then just want to return the row that the players details are on (identified by their email) so far ive not found a function to do this straight off.

From googling around ive managed to cobble the following code together:

'select @row_val:=email from scores where email = ' myEmail ' order by score desc; select count(email) as pos from scores where email < @row_val';

I don't understand it entirely but at any rate it works in the command prompt 🙂 - but wont when called from my php page 🙁
I'm not sure why, anyone have any ideas?

Thanks

[ragefu]

If you are using single quotes in the SQL statement, you need to use double quotes around the SQL statement, which you are currently not using.

Also, you can't execute 2 queries like that at the same time from php, at the console, yes. Break it up into separate queries.

[laserlight]

I have a table that holds players scores for a game im trying to make,
what i want to do is return where a particular player is in that list ordered by high score.

Return what where a particular player is in what list?
What is your table like?

[am024196]

the columns that im interested in are email and score held in a table called teams,
the layout is pretty much like this:

         |      email            |  score   |
            [email][e-mail][/email]          100
           [email][e-mail][/email]          99
            [email][e-mail][/email]            72
            [email][e-mail][/email]           26
            [email][e-mail][/email]          10

So if i was logged in as [e-mail] i want the query to return 3, as [e-mail] is in third place - i probably should have said 'table' instead of 'list' dont know if i confused things there

Thanks for your help

[laserlight]

Oh, ok.

I would do it this way:

SELECT COUNT(*)+1 FROM teams WHERE score >= (SELECT score FROM teams WHERE email='$email');

Though chances are you're using a version of mysql that doesnt allow for subselects, but you can use PHP to turn it into 2 queries.

[am024196]

YOU ARE A STAR!!

THANK YOU!!

it works perfectly! 😃😃😃