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