PHP Grouping an Array with Multiple Dimensions from database results

Question

I have an array as a result of a database query. Lines include two dimensions and some metrics. Metrics must be summed by dimension groups.

Here is an example raw data array in table view:

Here is the exact array:

array(13) {
  [0]=>
  array(6) {
    ["source_name"]=>
    string(8) "A"
    ["week"]=>
    string(2) "10"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(0)
    ["lost"]=>
    int(1)
    ["draw"]=>
    int(0)
  }
  [1]=>
  array(6) {
    ["source_name"]=>
    string(8) "A"
    ["week"]=>
    string(2) "10"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(1)
    ["lost"]=>
    int(0)
    ["draw"]=>
    int(0)
  }
  [2]=>
  array(6) {
    ["source_name"]=>
    string(8) "A"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(1)
    ["lost"]=>
    int(0)
    ["draw"]=>
    int(0)
  }
  [3]=>
  array(6) {
    ["source_name"]=>
    string(8) "A"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(1)
    ["lost"]=>
    int(0)
    ["draw"]=>
    int(0)
  }
  [4]=>
  array(6) {
    ["source_name"]=>
    string(8) "A"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(0)
    ["lost"]=>
    int(1)
    ["draw"]=>
    int(0)
  }
  [5]=>
  array(6) {
    ["source_name"]=>
    string(8) "A"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(0)
    ["lost"]=>
    int(1)
    ["draw"]=>
    int(0)
  }
  [6]=>
  array(6) {
    ["source_name"]=>
    string(8) "A"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(1)
    ["lost"]=>
    int(0)
    ["draw"]=>
    int(0)
  }
  [7]=>
  array(6) {
    ["source_name"]=>
    string(7) "B"
    ["week"]=>
    string(2) "10"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(0)
    ["lost"]=>
    int(1)
    ["draw"]=>
    int(0)
  }
  [8]=>
  array(6) {
    ["source_name"]=>
    string(7) "B"
    ["week"]=>
    string(2) "10"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(1)
    ["lost"]=>
    int(0)
    ["draw"]=>
    int(0)
  }
  [9]=>
  array(6) {
    ["source_name"]=>
    string(7) "B"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(0)
    ["lost"]=>
    int(1)
    ["draw"]=>
    int(0)
  }
  [10]=>
  array(6) {
    ["source_name"]=>
    string(7) "B"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(1)
    ["lost"]=>
    int(0)
    ["draw"]=>
    int(0)
  }
  [11]=>
  array(6) {
    ["source_name"]=>
    string(9) "C"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(1)
    ["lost"]=>
    int(0)
    ["draw"]=>
    int(0)
  }
  [12]=>
  array(6) {
    ["source_name"]=>
    string(9) "C"
    ["week"]=>
    string(2) "11"
    ["picks"]=>
    int(1)
    ["won"]=>
    int(1)
    ["lost"]=>
    int(0)
    ["draw"]=>
    int(0)
  }
}

Here is what I expect to get as output:

What is the best way to get that output?

Thanks.

Answer 1

You can simply do foreach over here like as

$result = [];
foreach($data as $key => $value){
    $hash = $value['source_name'] ."_". $value['week'];

    if(isset($result[$hash])){
         $result[$hash]['picks'] += $value['picks'];
         $result[$hash]['won'] += $value['won'];
         $result[$hash]['lost'] += $value['lost'];
         $result[$hash]['draw'] += $value['draw'];
    }else{
         $result[$hash] = $value;
    }
}
print_r(array_values($result));

Answer 2

You mentioned this array is a result of a database query. Thus, you should not be iterating through the results like this, your focus should be in how you are obtaining these results from your database as SQL can do all this math for you with better performance.

To show you this, imagine your database table is named my_table and has all the information you posted above: (source_name, week, picks, won, lost, draw) :

+-------------+------+-------+-----+------+------+
| source_name | week | picks | won | lost | draw |
+-------------+------+-------+-----+------+------+
| A           | 10   | 1     | 0   | 1    | 0    |
+-------------+------+-------+-----+------+------+
| A           | 10   | 1     | 1   | 0    | 0    |
+-------------+------+-------+-----+------+------+
| A           | 11   | 1     | 1   | 0    | 0    |
+-------------+------+-------+-----+------+------+
| A           | 11   | 1     | 1   | 0    | 0    |
+-------------+------+-------+-----+------+------+
| A           | 11   | 1     | 0   | 1    | 0    |
+-------------+------+-------+-----+------+------+
| A           | 11   | 1     | 0   | 1    | 0    |
+-------------+------+-------+-----+------+------+
| A           | 11   | 1     | 1   | 0    | 0    |
+-------------+------+-------+-----+------+------+
| B           | 10   | 1     | 0   | 1    | 0    |
+-------------+------+-------+-----+------+------+
| B           | 10   | 1     | 1   | 0    | 0    |
+-------------+------+-------+-----+------+------+
| B           | 11   | 1     | 0   | 1    | 0    |
+-------------+------+-------+-----+------+------+
| B           | 11   | 1     | 1   | 0    | 0    |
+-------------+------+-------+-----+------+------+
| C           | 11   | 1     | 1   | 0    | 0    |
+-------------+------+-------+-----+------+------+
| C           | 11   | 1     | 1   | 0    | 0    |
+-------------+------+-------+-----+------+------+

If you run the following SQL query, you will get your desired results without you having to worry about iterating or looping later.

SELECT source_name, week, sum(picks), sum(won), sum(lost), sum(draw)
FROM my_table 
GROUP BY source_name, week 
ORDER BY source_name

RESULT:

+-------------+------+------------+----------+-----------+-----------+
| source_name | week | sum(picks) | sum(won) | sum(lost) | sum(draw) |
+-------------+------+------------+----------+-----------+-----------+
| A           | 10   | 2          | 1        | 1         | 0         |
+-------------+------+------------+----------+-----------+-----------+
| A           | 11   | 5          | 3        | 2         | 0         |
+-------------+------+------------+----------+-----------+-----------+
| B           | 10   | 2          | 1        | 1         | 0         |
+-------------+------+------------+----------+-----------+-----------+
| B           | 11   | 2          | 1        | 1         | 0         |
+-------------+------+------------+----------+-----------+-----------+
| C           | 11   | 2          | 2        | 0         | 0         |
+-------------+------+------------+----------+-----------+-----------+

Check out this SQL FIDDLE to help you understand it.