PHP Grouping an array with multiple dimensions from database results

Question

I have an array as a result of a database query. The lines include two dimensions and some indicators. Metrics should be summarized by measurement groups.

Here is an example of an array of raw data in a 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 this conclusion?

Thanks.

Answer 1

You can just do foreach here 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 that this array is the result of a database query. Therefore, you should not repeat these results, so you should focus on how you get these results from your database, since SQL can do all this math for you with better performance.

To show this, imagine that your database table is named my_table and contains all the information you specified 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 the desired results without fear of repeating or repeating the loop 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 | +-------------+------+------------+----------+-----------+-----------+ 

See SQL FIDDLE to help you understand.