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.

So, loop over the array and sum the values into a new array. It's not clear to me where you are stuck. — GolezTrol
– GolezTrol, Commented Sep 21, 2015 at 10:50
@CodeGodie actually I couldn't figure out how can I group and sum without multiple loop. And, yes, it's a result of a database query. — user1488895
– user1488895, Commented Sep 21, 2015 at 11:01
Why does it have to be done without multiple loops? Also, if it's a database query result, why not add the grouping to the query? Databases are good at that stuff and it saves in the amount of data that has to be sent from the database to PHP. — GolezTrol
– GolezTrol, Commented Sep 21, 2015 at 11:05
I agree with @GolezTrol you need to focus on your database query rather than iterating through the results. Sql queries can reproduce what you want by using COUNT GROUP_BY ORDER_BY, etc... — CodeGodie
– CodeGodie, Commented Sep 21, 2015 at 11:08

Narendrasingh Sisodia · Accepted Answer · 2015-09-21 11:49:33Z

5

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

edited Sep 21, 2015 at 11:49

answered Sep 21, 2015 at 11:04

Narendrasingh Sisodia

21.4k6 gold badges51 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GolezTrol Over a year ago

I don't understand what is happening in that else branch... Shouldn't that be $result[$hash] = $value;?

Narendrasingh Sisodia Over a year ago

OMG! I didn't seen that I've updated my answer @GolezTrol. Thanks for pointing out I just posted answer and closed the window

Narendrasingh Sisodia Over a year ago

@GolezTrol Even I'm thinking why it's not been downvoted

user1488895 Over a year ago

I have stopped to sum week numbers, and that was what I want! Removed this line: $result[$hash]['week'] += $value['week']; Thanks a lot!

Narendrasingh Sisodia Over a year ago

Oh yes I'm in so much hurry that I didn't seen my answer

|

CodeGodie · Accepted Answer · 2015-09-21 13:05:15Z

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.

Collectives™ on Stack Overflow

PHP Grouping an Array with Multiple Dimensions from database results

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related