Calculate overall and division ranking via arrays

Question

This is a follow-up to Detect future duplicate values while iterating through MySQL results in PHP.

I have an SQL query which produces the results:

team_id   division_id   wins
-------------------------------
10        2             44
9         2             42
5         1             42
2         1             42
3         1             41
11        2             40
1         1             36
8         2             31
7         2             29
12        2             24
4         1             20
6         1             18

I'm trying to calculate a given team's overall and divisional rankings.

For example, team_id = 1:

Overall:       7
Divisional:    4

For team_id = 3:

Overall:       5
Divisional:    3

For team_id = 9:

Overall:     T-2  //must indicate tie
Divisional:    2

As you can see from the linked previous question/answer, I can calculate the Overall rank just fine. The issue comes with calculating the divisional rank as well (including properly handling ties).

I've tried storing the results in a multi-dimensional array like $arr['wins']['division_id']['team_id'], such as...

44 =>  2 => 10
42 =>  1 =>  5
             2
       2 =>  9
41 =>  1 =>  3
40 => 11 =>  2
...

But am stuck as to how to iterate through and get my two respective ranks, as well as detecting ties appropriately for each.

Answer 1

Try something like this:

// Dataset as defined on question
$arr = array(
array('team_id' => 10, 'division_id' => 2, 'wins' => 44),
array('team_id' => 9, 'division_id' => 2, 'wins' => 42),
array('team_id' => 5, 'division_id' => 1, 'wins' => 42),
array('team_id' => 2, 'division_id' => 1, 'wins' => 42),
array('team_id' => 3, 'division_id' => 1, 'wins' => 41),
array('team_id' => 11, 'division_id' => 2, 'wins' => 40),
array('team_id' => 1, 'division_id' => 1, 'wins' => 36),
array('team_id' => 8, 'division_id' => 2, 'wins' => 31),
array('team_id' => 7, 'division_id' => 2, 'wins' => 29),
array('team_id' => 12, 'division_id' => 2, 'wins' => 24),
array('team_id' => 4, 'division_id' => 1, 'wins' => 20),
array('team_id' => 6, 'division_id' => 1, 'wins' => 18));


$divisionTeam = array();
$divisionWins = array();
foreach($arr as $team) {
    $divisionTeam[$team['division_id']][] = $team['team_id'];
    $divisionWins[$team['division_id']][] = $team['wins'];
}



echo "<pre>";
foreach (array_keys($divisionTeam) as $division_id) {
    $rank = 0;
    $prevWins = -1;
    echo "DIVISION $division_id \n";
    foreach ($divisionTeam[$division_id] as $index => $team_id) {

       if ($prevWins == $divisionWins[$division_id][$index]) {
           echo $team_id . " T - " . $rank . "\n";
       }
       else { 
           $rank++;
           echo $team_id . " " . $rank . "\n";
       }
       $prevWins = $divisionWins[$division_id][$index];

    }
}
echo "</pre>";