php - Return a row number from an ordered list with MySQL -

i'm working on implementing "high scores" section in game. high scores, i'm displaying top ten. no problem. problem want show user rank is. user ranked 300, there way mysql order list, find username, , somehow return how far down list or have copy entire database array or , count rows until hit user?

well, if displaying top 10, think simplest , quickest way assign rank in php. so, fetch list of top 10 scorers in desc order of scores, read list php array , rank (array index + 1).

however, if wish assign ranks users there way of doing this; used in 1 of implementations. i'm not sure though if there direct way of doing so. here:

1. create temporary table, user_rank following columns:
   • id pk auto_increment
   • user_id
   • rank smallint unsigned
2. retrieve list of users in desc order of scores, top scorers on top
3. store list step2 in temp table user_rank
4. user_rank.id gives rank every user

you may steps2-3 in same query, like:

insert `user_rank` (`user_id`, `rank`) select `id`, `score` `high_scores` order `score` desc; 

hope helps.