I learned something today...

[iceflyin]

NEVER name a column "from"...
SELECT * FROM userinfo WHERE from=$user


Ok, now for my question... Say I have a table that goes [userid] [score], and I want to get the user's rank. Is there a way to do that directly in mysql? Except pulling the entire table and sorting and counting in php?

[guelphdad]

select count(*) + 1 as total
from yourtable
where score <
(SELECT score
FROM yourtable
WHERE userid = $userid
)


assuming you know the userid in question.

and on your other point, yup best to bookmark the reserved word list so you don't run into trouble that way.

[iceflyin]

Works nicely to get their score, but... That wasn't what I was asking.

//print_r on the fetch array:
Array ( [total] => 1 )

I'm not looking for the score, I'm looking for the "Rank." The users rank is in relation to the scores of all the other users scores in the table. Is the only way to do this still to pull the entire table of scores and userids, sort it in php, then rank it based on the scores?

[Fumigator]

I believe Guelph's code does get rank. It counts up the number of rows in the table with a score lower than the userID's score.

Actually yeah you'd want to get the number of rows that are higher than the userID's score instead. So if there are 10 rows with a higher score, then count(*) + 1 = 11, which is the rank. So change the < to a >.

Unless the score is a golf score :p

[guelphdad]

Thanks for the catch Fumigator.