CODEWITHSUNDEEP
phpmysqlarraysdatabasemultidimensional-array
user1488895
user1488895

Reputation: 71

PHP Grouping an Array with Multiple Dimensions from database results

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: enter image description here

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: enter image description here

What is the best way to get that output?

Thanks.

Upvotes: 5

Views: 90

Answers (2)

CodeGodie
CodeGodie

Reputation: 12132

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.

Upvotes: 3

Narendrasingh Sisodia
Narendrasingh Sisodia

Reputation: 21437

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));

Upvotes: 5

Related Questions