Leaderboard with equal places

[jingo_man]

how do i make a leaderboard that is capable of handling equal scores from a particular result?

 

we use following points allocations:

winner: 5pts

second: 3pts

third: 2pts

fourth: 1pt

 

however, if 2nd and 3rd have tied scores, i would like to combine their point allocations together and divide by 2, i.e. (3+2)/2=2.5points each.

 

initial query for competition ranking:

$q_lastcomp = "SELECT p.p_id, p.p_surname, p.p_forename, p.p_handicap, r.r_id, rp.rp_score, rp.rp_handicap, (rp.rp_score-rp.rp_handicap) as net_score ";
$q_lastcomp .= "FROM dan_roundplayed rp, dan_round r, dan_player p ";
$q_lastcomp .= "WHERE r.r_id = rp.rp_rid ";
$q_lastcomp .= "AND p.p_id = rp.rp_pid ";
$q_lastcomp .= "AND r.r_id = 6 ";
$q_lastcomp .= "ORDER BY net_score";
$r_lastcomp = mysql_query($q_lastcomp) or die(mysql_error());

 

i then loop through this resultset in this order (top down), and do some more calculations to adjust the players golf handicaps based on these results too. basically, if there are 4 players, the winner has beaten 3 others, second has beaten 2 others, down to last place who has beaten no-one.

the number of players you have beaten is multiplied by 10% of their handicap (not precisely this value, but something like it and makes easier to explain), then this adjustment is subtracted from the handicap to make a new handicap for the next comp.

 

this works fine, so long as no-one is tied. but if 2nd and 3rd tie, then both players have effectively only beaten 1 other player (last placed). so i would want both to do "hcap - (1*(hcap*0.1))" for their handicap adjustment calculations.

in my current code, even though they were tied, 2nd would have: "hcap - (2*(hcap*0.1))"

3rd would have: "hcap - (1*(hcap*0.1))"

 

my current loop code is:

    $total_players = mysql_num_rows($r_lastcomp);
    
    $i = 1;
    while($row = mysql_fetch_array($r_lastcomp)){
        echo "<br>";
        $round_hcap = round($row[p_handicap]);
        SWITCH ($round_hcap){
            CASE $round_hcap >= 0 AND $round_hcap <= 19:
                $multiplier = 0.1;
                break;
            CASE $round_hcap >= 20 AND $round_hcap <= 29:
                $multiplier = 0.2;
                break;
            CASE $round_hcap >= 30 AND $round_hcap <= 39:
                $multiplier = 0.3;
                break;
            CASE $round_hcap >= 40 AND $round_hcap <= 49:
                $multiplier = 0.4;
                break;
        }
        $hcap_reduce = ($total_players-$i)*$multiplier;
        $new_hcap = $row[p_handicap]-(($total_players-$i)*$multiplier);
        $sqlstr = "INSERT INTO dan_handicap VALUES (". $row[p_id]. ",". $row[r_id]. ",". $row[p_handicap]. ",". $i. ",";
        $sqlstr .= $total_players. ",". $hcap_reduce. ",". $new_hcap. ");";
        $sqlstr2 = "UPDATE dan_player SET p_handicap = ". $new_hcap. " WHERE p_id = ". $row[p_id]. ";";
        echo $sqlstr. "<br>";
        echo $sqlstr2. "<br>";
        $i++;
    }

 

can anyone help with this please?

 

my initial thoughts are i am going to need a drastic adjustment to the initial query add in position number in the comp, i.e. this would produce

posn | name | score

1 | winner | 10

2 | tied 2nd | 8

2 | tied 2nd | 8

4 | last place | 5

 

the second section of code could then use this "position number" for the multiplier part of the calculations.

 

apologies for the length of the post, but this covers the whole requirements.

 

regards

 

jingo_man

 

[gizmola]

Just because you queried the database doesn't mean you have to fetch and process.  You can fetch all the rows into an array and then foreach through them.  This would allow you to implement your "posn" easily in a simple foreach loop.

 

$score = -1;
$pos = 0;
while($row = mysql_fetch_array($r_lastcomp)){
  if ($row['score'] != $score) {
    $pos++;
    $score = $row['score'];
  }
  $row['pos'] = $pos;
  $rows[] = $row;
}

// Now you foreach through rows[] and you have added the 'pos' column which tells you their rank, accounting for ties

foreach ($rows as $row) {
  // ... your handicap computation code here

[jingo_man]

thanks gizmola

 

yeah, suppose that is equally valid. essentially looping through it twice i guess, but 2nd loop is against a local array. so could pre-process on 1st loop when retrieving from database to include the position field.

 

is the final line

$rows[] = $row;

the bit that reads everything from a given line into a particular array element? why is there nothing in the square braces? i.e. $rows[]

 

can i add the additional "position" as a new element to the array while i am at it?

 

i think the loop would need to check the the current score is the same as the previous row's score. if they are the same, then that position must be tied. or is my logic wrong?

 

cheers

 

[gizmola]

That line dynamically adds an element to the $rows array.  No you can't use position as the array key, because in doing so, you would overwrite the previous value every time you had a position with more than one element.  var_dump() on an array like that is a good way to see what it looks like once you've built it.

[jingo_man]

many thanks for the help gizmola.

 

with the info you have provided, i can mark this question as solved. i made a slight alteration, but your base code was the guiding light i needed.

 

cheers