SQL and PHP to rank players in game

[intellix]

Hey again, project is coming along quite well now thanks to the help from people who have answered some questions previously much appreciated

I now have another problem or question I guess.

Players are to be ranked on a statistics page which is based on population or another statistic which may be implemented later. 20 results per page.

Say there are 30,000 players on a server... and a player builds a building that increases his population and thus: rank from 25,000 to 5,000...
If I were to store ranks in a table for each player and then display them in a table on the page... would I need to do 20,000 updates for each player that this person overtakes... and perform this everytime someone overtakes a large amount of players ooor is there another way? perhaps caching or something... im not quite sure... thanks

 

[intellix]

the players are currently ordered by population so person with 10 will be higher than one with 5.

It's just that the users will want to know their rank number so they can compete with friends or enemies etc.

kind of like

Rank | Player | Clan | Population | Villages
1 me - 1002 2
2 ret - 1000 2
3 yot - 900 1
4 yooro - 700 2

Sure its easy enough to just do a loop and have $x++ as you display the columns but when theres a paging system... (and also for their rank to be displayed on their profile page) I guess the rank would have to be stored somewhere so it can be displayed in other places and when your on the statistics page you can search by rank... liike (view rank 1)

 

[intellix]

just read through your post again and you said:

you would simply order by score and use PHP etc to create a rank column (i.e. 1-x where x is the number returned by your query)

i'm confused what you mean by number returned by your query... what number? :/

perhaps you mean... count(results) that are less than the population of you + 1 to get your rank, then the other 19 players on the statistics page rank = your rank and +1 each person you display... sounds ok actually I think ... but then what if there are 5 other players with the same population as you... they aren't included in the less than population query... actually duh... can't I just get the rank from the offset? damn im stupid :s

 

[intellix]

yeah... i've got the rank of the player using the page limiter (duh)

$offset = ($pageNum - 1) * $rowsPerPage;

then $x = $offet

while there are results
$x + 1

display the $x as the rank...

Got the ranks being displayed correctly on the statistics page... but how would you search this for one user or one rank but also have the ranks displayed like they are? is it possible to search a set of results saaay.... 20k results, you're currently viewing results 1501 - 1521... but you want to display player who is ranked 1... ok you post the offset and work from that... but what about a player name? cause now your just querying the database for one result and so the rank will display as 1 as there is only one result... so instead would need to do what is being done now but go to the user from the current set of results :S

what if you did a loop so that it would check the results, if the user isn't displayed from there, go to the next 20 and look there... and so on, until the user is reached and the loop can stop... but seems a bit much of processing to search a user... or did a count on users with population less than you, and start the loop from there... would be a lot less than doing it from 0

 

[intellix]

yeah, im currently doing LIMIT to get the top players.

Its currently like:
1 - bob - pop 20
...
20 - billl - pop 12

then next page:
21 - frank - pop 11
...
40 - food - pop 8

and so on... got a search by RANK feature which basically changes the LIMIT. so if you search for rank 35 it will show you 21 - 40 which works great at the moment... but the ranks aren't -tied- to a player so searching for a player by name on the statistics page won't display their rank as the rank is currently determined by the LIMIT thingy... and unable to display their rank on their profile page for the same reason.

Was thinking of perhaps having a statistics page, which gets updated every (5minutes?) by getting crontabs to loop through the population of each player and ranking them. Then the rank would be stored in the database somewhere for displaying on other pages like their profile

 

[intellix]

so that would grab one result from the table where one is bigger than the other? then I would have to do a complete drop of the old rank table, and create another using what I get there to update it?