SQL and PHP to rank players in game

intellix

intellix

intellix

Associate
Joined
7 Dec 2007
Posts
302
Location
Derbyshire, Tibshelf
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 :rolleyes: 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
 
Just have a nominal score instead of a rank (I assume you have something like this to work out the ranks anyway). Then you would just order by this column. An example might make it clearer. the first table will have a rank column whilst the second would use the score based approach.
So table1 = playerid, username, rank
table 2 = playerid, username, totalscore (which determines the rank)
Tab1
1,playerA,1
2,playerB,2
3,playerC,3

Tab2
1,playerA,10000
2,playerB,6000
3,playerC,3000

Now if playerC suddenly plays better than the other two and his score (and hence rank) surpasses the other two players in the table you would need to do as many updates as the players he rises above. In the second example you would just do one update on his row and then when you select the ranked players 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) depending on where in the result said the player was.

NB: you might not even need or want to display the score column but this would still work
i.e. select username from tab2 order by score DESC
 
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)
 
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
 
Last edited:
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
 
Last edited:
I'm a tad confused. Are you saying you want to just return the number one player or a specific player for a player name. Assuming

Can't you just run SELECT TOP 1 * FROM Tab ORDER BY Population DESC (you might have to use LIMIT 1 in mySQL I think).
If you want to return a specific player then
SELECT * FROM Tab WHERE Player = 'playerName'.

Or do you want to work out a specific players rank by name?
 
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
 
Ahh. Yeah you could store it, but a bit of a frig but this should work. Whatever value this returns will be the rank of player with name = 'PlayerName' (NB I am assuming playername is unique)
SELECT COUNT(1)+1
FROM tab t1, tab t2
WHERE t1.Player <> t2.Player
AND t1.Player = 'PlayerName'
AND t2.Population>t1.Population
 
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?
 
Yeah, you could just calculate this when needed for that page. It essential works out how many people are higher ranked than you and then adds one for your rank (obviously test it first). to make the update faster (i.e. to update all the ranks at once the following should work). Create a temp table and then use this to update everything
SELECT COUNT(1)+1 as PlayerRank, t1.Player
INTO tempTableForUpdate
FROM tab t1, tab t2
WHERE t1.Player <> t2.Player
AND t1.Player = 'PlayerName'
AND t2.Population>t1.Population
GROUP BY t1.Player

then
update Table
from table, tempTableForUpdate
set table.rank = tempTableForUpdate.playerRank
WHERE table.Player = tempTableForUpdate.player
 
You must log in or register to reply here.
Back
Top Bottom