Getting the totals per month inside Php loop

PHP
1

I am looping through a table to get the total entries for each month and will then output the array to a chart. Creating the array and chart works…but I am struggling to get the totals for each month. I am not sure how to construct the loop. Here is what I have so far:

 $sql = "SELECT YEAR(member_since) AS year, MONTH(member_since) AS month FROM 
  members WHERE member_since > DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY year, 
  month";
 $stmt = $conn->prepare($sql);
  $stmt->execute();
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);


foreach ($result as $row){
      if ($row['month'] == '1'){
          $january_total += 1;
      }
   .....

   }

  $chart1 = array(
            array('Month', 'Totals'),
             array('Januray Total',$january),
            ..................
          );

At the end I want totals per month according to the date member_since

2

"SELECT YEAR(member_since) AS year, MONTH(member_since) AS month FROM members WHERE member_since > DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY year, month"
This query should return exactly 1 row per month and year. That’s what GROUP BY is intended for.
"SELECT YEAR(member_since) AS year, MONTH(member_since) AS month, COUNT(id) AS total FROM members WHERE member_since > DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY year, month"

(Use the database engine to do this for you.)

3

Thank you I will try this!

4

Do I display the totals lfor each month like this:

$row[‘total’]

5

yup.

1 Like
6

What if there are no totals for some of the months…will it contain zero?
Also… Would the month January be 01 or 1

foreach ($result as $row){
if ($row[‘month’] == ‘1’){
$janTotal = $row[‘total’];
}
}

7

No, the only rows returned will be rows that exist in your database.
How far back would you expect the code to go?

Depends on your database engine; Assuming you’re using MySQL, MONTH() returns a value with no leading zero.

8

Yes I am using mysql and I assume that there will be no leading zero.

The bar chart will give the total subscriptions per month for the current year. But I need to consider that there might not be any subscription or activity for some months.

9

Ok thanks the code is working and the chart displays correctly. I just have one more question:

Does INTERVAL 1 YEAR take todays month and go back 12 months? OR does it grab the rows from this years January. I assume it goes back exactly 12 months right…which means if I run the script middle of the year I should on display the last 6 months.

10

From when to when?

If you want 1 year’s worth of data, then what you’ve got works.

11

Ok thank you - you’ve been helping me a lot the past few months and I really appreciate it :slight_smile:

1 Like
12

I wouldn’t do it like that, but like this:

$totals = array_column($row, 'total', 'month');

Assuming the following data:

month total
1 100
3 810

you will get an array like this:

$totals = [
    '1' => '100',
    '3' => '810',
];

so for example the total for March is in $totals[3], and assuming you’re on PHP 7.x you could use $totals[3] ?? '0' to display the result for March, or 0 if there is no result for March.

13

Careful there, Rémon.

Selecting a Year interval will mean the Month column potentially has a duplicate value - running this query today will pull records from October 4-31 of 2018, as well as October 1-4 of 2019.

14

True, nice catch! But then maybe the original code has that problem as well? I don’t see it taking years into account either, it just looks at months.

1 Like
15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.