True ladder system in PHP...confused.

[rogwilco]

I am making a leaderboard site for our local club and have just about got everything either designed opr working apart from the main part of the site which is the ladder.

The ladder system is the only option for the site. I have spent most of this week with pen and paper but for the life of me cannot seem to design the damn thing. I'll give an example below to enlighten you, and show you were my problem lies;

Say we have the following leaderboard (A):

Rank Name
1 Andy
2 Roger
3 Ian
4 Jane
5 Peter
6 Susan
7 John
8 Mike
9 Amy
10 Lisa

If John beats Susan the leaderboard would be as follows (leaderboard (B));

Rank Name
1 Andy
2 Roger
3 Ian
4 Jane
5 Peter
6 John
7 Susan
8 Mike
9 Amy
10 Lisa

If Amy beats Ian (leaderboard (C));

Rank Name
1 Andy
2 Roger
3 Amy
4 Ian
5 Jane
6 Peter
7 Susan
8 John
9 Mike
10 Lisa

So to sum it up, If you beat the person you play, you go above them, which in turn means that everyone's rank will go up by 1 until you get to the person who originally was sat below you. This is a classic ladder in Squash or Tennis.

For the life of me though the pseudo code for this seems impossible. Well obviously, nothing is impossible but it certainly feels like that at the moment.

My next point is how I am going to create this leaderboard from my database.

When someone joins they will pick their skill from 1-10 (10 being the best) then they will automatically be given a skill_rank number (this will increment with every new member) which determines their position in their chosen skill. The purpose of skill is to let a newcomer join the ladder at an appriopiate level. Skill_rank simply is the rank you are within that skill level.

So for leaderboard (A) the database contents might be as follows (in random order);

Name Skill Skill_Rank
Susan 5 1
Andy 10 1
Roger 10 2
Jane 9 1
Lisa 1 1
John 2 1
Mike 2 2
Ian 10 3
Peter 9 2
Amy 2 3

The SQL statement would be as follows:

SELECT * FROM tennis ORDER BY skill, skill rank.

Now this all works fine until the first match is played.... Everyone between the two players will need their record altering!!!?? If I simply made the challenger and the oponent swap places after a win, that would be easy to implement, but alas this isn't how a 'proper' ladder works. The winner HAS to go one above the loser UNLESS the winner was already above, in which case there is no change.

Any thoughts? BTW field skill_rank is not set in stone so I am open to any variations on that? I has thinking on having a rank field but surely that goes against database design, i.e. DB are sorted by queries and not stored in a sorted order.

Thanks for any help, I REALLY appreciate it!!!

Roger.

 

[Itshim]

This is some pseudo code which gives you an idea of the logic that can be used to create your ladder system:

select ranks of the 2 competing players

if (winner_rank > loser_rank) {

  select all player_ids and ranks >= loser_rank
  update winner_rank = loser_rank

  loop through select result {
    if (current_id != winner_id) {
      update current_id_rank = current_id_rank+1
    }
  }
}

Hopefully this will give you some ideas on how to proceed.

Itshim

 

[Itshim]

Opps... That does not seem to work, I just re-read your post, and realized a problem with my idea, back to the drawing board, sorry. I will try again and let you know if I come up with something.

 

[Itshim]

if (winner_rank > loser_rank) {

  update winner_rank = loser_rank
  update loser_rank = loser_rank+1

  Select all player's ids and ranks 
  where rank >= loser's new rank 
  and player_id != loser_id
  
  new_rank = loser_rank+2

  loop through select result {
    update current_rank = new_rank;
    new_rank++;
  }
}

Ok, I think this logic will work, I've gone over it in my head and do not see a problem at the moment.

You may also want to check (inside the loop) if the new rank is equal to the current rank, if so do not update and exit loop. This will speed up processing and stop excess updating.

 

[rogwilco]

Yes thats seems to be getting close to what I want. I will try out coding a mockup this mrnong and tell you later how I get on. Thanks a lot.

 

[dpk136]

yes that should work except for in this part
" Select all player's ids and ranks
where rank >= loser's new rank
and player_id != loser_id""
I think you might need to check if it is less than the new rank of the winner also. That way no one above the new winner gets bumped out.

David Kuhn
------------------

http://www.davidpkuhn.com