A ranking leaderboard!??

[rogwilco]

I am trying to design and implement an online leaderboard in MySQL and PHP. Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db. It is the query you write/code which does the sorting. How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc. However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical. You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking? Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before. I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

Please can anyone give me any ideas or tutrials on the net that cover this idea.

Thankyou,

Roger.

 

[TonyGroves]

Suppose you have a table with a record for each player containing his total score. It's very simple to show the top 10:[tt]
SELECT *
FROM players
ORDER BY score DESC
LIMIT 10
[tt]
The syntax should be fairly self-explanatory.

 

[TonyGroves]

Sorry, that should be:

Suppose you have a table with a record for each player containing his total score. It's very simple to show the top 10:[tt]
SELECT *
FROM players
ORDER BY score DESC
LIMIT 10
[/tt]
The syntax should be fairly self-explanatory.

 

[TonyGroves]

Also, the recent thread

http://www.tek-tips.com/viewthread.cfm?qid=1037986&page=1

shows a solution for getting a leaderboard which accounts for tied places, so that if for example two players tie for second place, the rankings go 1,2,2,4 etc.

 

[rogwilco]

Yes I understand the LIMIT option and thanks for the quick reply but what I was getting at was if a user had logged into the site and wanted to see their 'ranking' on the board, without having to scroll down a 1000+ other people.

My point is, how will SQL ever know the position of someone so it can output it to screen?

 

[TonyGroves]

You could take the solution from the other thread I referred to, which produces a result set in the form "position, player, score", and just add a WHERE clause to it to pick out the player you want. And if your records contain the total score for each player, instead of having to calculate the scores, you could simplify that query further.

If you need help with that, let us know.

 

[rogwilco]

Thanks Tony I'll have a proper read through that link. On my first read through I got lost. I'll get back to you if I need anything clarifying.

Roger.