PHP: foreach loop by id and month and add values

Question

I've been trying to solve this issue I'm currently having. We have a table with id, productid, userid, count and downloaddate. Something like below to understand the structure and what the results look like.

+----+-----------+--------+-------+---------------+
| id | productid | userid | count | downloaddate  |  
+----+-----------+--------+-------+---------------+
|  1 |         9 |    231 |     2 | October 2017   |  
|  2 |         8 |    230 |     1 | October 2017  |  
|  3 |         9 |    287 |     1 | October 2017  |  
|  4 |         9 |    200 |     2 | November 2017 |  
+----+-----------+--------+-------+---------------+

So what I want to loop is to get all the productid and return the total number of count for the given month which is recorded in the downloaddate column.

To return this:

+------------+-------+---------------+
| productid  | count | downloaddate  |  
+------------+-------+---------------+
|          9 |     3 | October 2017   |  
|          8 |     1 | October 2017  |  
|          9 |     2 | November 2017 |  
+------------+-------+---------------+

Is it possible to do this? I've tried this but it's returning the total count for the productid

$sum = array();
$emparray = array();

foreach ($downloads as $download){ 
   $emparray[] = $download;
}


foreach ($emparray as $downloaded){ 
   if (!isset($sum[$downloaded->id])) { 
      $sum[$downloaded->id]['count'] = $downloaded->count;
      $sum[$downloaded->id]['downloaddate'] = $downloaded->downloaddate;
    } else { 
      $sum[$downloaded->id]['count'] += $downloaded->count;
      $sum[$downloaded->id]['downloaddate'] = $downloaded->downloaddate;
     } 
 }

Any help will be appreciated!

EDIT

This is my sql query:

$downloads = $wpdb->get_results( 
             "
                SELECT ast.*, dl.userid, dl.count, dl.downloaddate 
                FROM $lead_table as ast
                JOIN $table_downloads as dl ON (dl.productid = ast.id)
              "
            );

This should be possible to do in only sql though, no need to waste time in PHP — JimL
– JimL, Commented Jan 28, 2018 at 19:35

splash58 · Accepted Answer · 2018-01-28 19:53:51Z

1

You can get the result by one query grouping by productid and downloaddate (I hope that October2017 is a typo in the question)

SELECT productid, sum(count) as count, downloaddate 
   FROM your_table
   GROUP BY productid, downloaddate

update. with your query it seems to be

SELECT ast.*, dl.userid, sum(dl.count) as count, dl.downloaddate 
    FROM $lead_table as ast
    JOIN $table_downloads as dl 
      ON (dl.productid = ast.id)
    GROUP BY dl.userid, dl.downloaddate

edited Jan 28, 2018 at 19:53

answered Jan 28, 2018 at 19:49

splash58

26.2k3 gold badges25 silver badges35 bronze badges

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

2 Comments

designtocode Over a year ago

Forgot to mention that I'm using 2 tables to get the data with JOIN. I'll add my SQL query to the question. Will I still be able to use GROUP BY and count()?

designtocode Over a year ago

Yes October2017 was a typo. It is inserting the month with date("F Y").

bardizba · Accepted Answer · 2018-01-28 19:35:22Z

1

i think you should edit your sql Query into this :

SELECT productid, sum(count) as count, downloaddate FROM your_table GROUP BY downloaddate

answered Jan 28, 2018 at 19:35

bardizba

562 bronze badges

1 Comment

designtocode Over a year ago

Forgot to mention that I'm using 2 tables to get the data with JOIN. I'll add my SQL query to the question. Will I still be able to use GROUP BY and count()?

One More Light · Accepted Answer · 2018-01-28 19:45:27Z

You have to be able to tell which month is given in the downloaddate (can you build a timestamp ? Or something else that tells you that "October2017" is the same that "October 2017".

Once you got that, you "just" have to store your "sum" in a 2D array with something like that:

$sum = array();
foreach ($downloads as $download) {
    // dateToTimeStamp: custom function who gives you the same number for October2017 or October 2017
    $timestamp = dateToTimeStamp($downloaded->downloaddate);
    if (!isset($sum[$timestamp])) {
        $sum[$timestamp] = [];
    }

    if (!isset($sum[$timestamp][$downloaded->id])) { 
        $sum[$timestamp][$downloaded->id]['count']        = $downloaded->count;
        $sum[$timestamp][$downloaded->id]['downloaddate'] = $downloaded->downloaddate;

        continue 1;
    }

    $sum[$timestamp][$downloaded->id]['count']       += $downloaded->count;
    $sum[$timestamp][$downloaded->id]['downloaddate'] = $downloaded->downloaddate; 
}

parsa · Accepted Answer · 2018-01-28 20:59:31Z

0

you can easily use this query and extract the data from php and use it there

 select productid,sum(count),downloaddate from tablename group by productid,downloaddate

edited Jan 28, 2018 at 20:59

answered Jan 28, 2018 at 20:11

parsa

9953 gold badges10 silver badges23 bronze badges

Collectives™ on Stack Overflow

PHP: foreach loop by id and month and add values

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related