Display unique results and count duplicate results multi-dimentional array

Question

Here is my multi-dimensional array:

Array
(
    [0] => Array
        (
            [H2H_Id] => T32
            [Team1_Id] => T4
            [Team1] => Juan Arraya - Max LePivert
            [Team2_Id] => T205
            [Team2] => Marco Grangeiro - Jeff Morneau
            [Winners_Id] => T4
            [MatchUps_Id] => M32
        )

    [1] => Array
        (
            [H2H_Id] => T39
            [Team1_Id] => T205
            [Team1] => Marco Grangeiro - Jeff Morneau
            [Team2_Id] => T4
            [Team2] => Juan Arraya - Max LePivert
            [Winners_Id] => T205
            [MatchUps_Id] => M32
        )

    [2] => Array
        (
            [H2H_Id] => T9
            [Team1_Id] => T3
            [Team1] => Marco Grangeiro - George Wilkinson
            [Team2_Id] => T4
            [Team2] => Juan Arraya - Max LePivert
            [Winners_Id] => T4
            [MatchUps_Id] => M9
        )

)

I want the output to be:

Juan Arraya - Max LePivert 1 vs. 1 Marco Grangeiro - Jeff Morneau
Juan Arraya - Max LePivert 1 vs. 0 Marco Grangeiro - George Wilkinson

So basically, I want to display the different match ups separately by using MatchUps_Id and print the names of the teams Team1 and Team2. Based on the Winners_Id I want to add a counter to keep track of the wins per team per match up.

Keep in mind that these results are coming from user inputs which will make the multi-dimensional array different every time I request input from the user passed on by field1 and field2.

This is what I have so far in code:

$query2 = "SELECT HeadToHead.H2HNo H2H_Id, H2HTeam1Id Team1_Id, H2HTeam1 Team1, H2HTeam2Id Team2_Id, H2HTeam2 Team2, WinnersId Winners_Id, MatchUps.MatchUpsNo MatchUps_Id
   FROM HeadToHead
   JOIN MatchUps ON HeadToHead.MatchUpsNo=MatchUps.MatchUpsNo
   WHERE ((H2HTeam1 LIKE '%$field1%' OR H2HTeam2 LIKE '%$field1%') AND (H2HTeam1 LIKE '%$field2%' OR H2HTeam2 LIKE '%$field2%'))";
$result2 = mysql_query($query2);
$num2 = mysql_num_rows($result2);

$arr2 = array();
if($num2 > 0) {
    while($row2 = mysql_fetch_assoc($result2))  {
        $arr2[] = $row2;
    }
}

I get the desired results from the code above.

I think I have done a lot of research on this matter but what I am trying to accomplish seems to be different than what is out there.

Any help is much appreciated.

Answer 1

You can group the matchups together by 'MatchUps_Id' like this:

foreach ($arr2 as $match) {

    // create some short ids to make the next part more readable    
    $id = $match['MatchUps_Id'];
    $t1 = $match['Team1_Id'];
    $t2= $match['Team2_Id'];

    if (!isset($matchups[$id][$t1]['name'])) {
        // initialize if this matchup has not yet been created
        $matchups[$id][$t1]['name'] = $match['Team1'];
        $matchups[$id][$t2]['name'] = $match['Team2'];
        $matchups[$id][$t1]['wins'] = (int) ($match['Winners_Id'] == $match['Team1_Id']);
        $matchups[$id][$t2]['wins'] = (int) ($match['Winners_Id'] == $match['Team2_Id']);
    } else {
        // increment wins if the matchup already exists
        $matchups[$id][$t1]['wins'] += $match['Winners_Id'] == $match['Team1_Id'];
        $matchups[$id][$t2]['wins'] += $match['Winners_Id'] == $match['Team2_Id'];      
    }
}

Using $t1 and $t2 as second-level keys allows you to increment the win count for respective teams without needing to know which one is which. The expression $match['Winners_Id'] == $match['Team1_Id'] returns a boolean, which will be implicitly cast to an integer 0 or 1 when used with +=, but must be explicitly cast using (int) when initializing.

After you have grouped your array and counted the wins, you can output the results like this:

foreach ($matchups as $matchup) {
    list($a, $b) = array_values($matchup);
    echo "$a[name] $a[wins] vs. $b[wins] $b[name]<br>";
}