MySql query to display data in Chartjs

Question

I have users table with a flag of free user or paid users and with date of registration. Something like

+------------+-----------+--------------+
| USERNAME   | FREE_USER | DATE_OF_REG  |
+------------+-----------+--------------+
| user_name1 | y         | 2018-01-14   |
| user_name2 | n         | 2017-12-23   |
| user_name3 | y         | 2017-12-12   |
| user_name4 | y         | 2017-11-19   |
| user_name5 | n         | 2017-09-13   |
| user_name6 | y         | 2017-08-27   |
| user_name7 | n         | 2017-07-06   |
+------------+-----------+--------------+

I need count of free_users and paid_users for last 6 months and if there is no users registered in a month it should give 0 as free and paid count.

I want to create line chart for both free user and paid users as like in this example (http://www.chartjs.org/samples/latest/charts/line/basic.html). For this I need data in comma (,) separated format like:

month_of_registration : ['August', 'September', 'October', 'November', 'December', 'January']

free_users_count_for_related_month : [0, 1, 0, 1, 0, 1, 1]

paid_users_count_for_related_month : [1, 0, 1, 0, 0, 1, 0]

How can I achieve this with mysql query in PHP.

I have written this query:

SELECT MONTH(date_of_reg) as monthno, MONTHNAME(date_of_reg) as month_name, YEAR(date_of_reg) as year,
COUNT(CASE WHEN `free_user` = 'y' THEN 1 END) AS free_user,
COUNT(CASE WHEN `free_user` = 'n' THEN 1 END) AS paid, 
COUNT(CASE WHEN `free_user` != '' THEN 1 END) as total_users
FROM tbl_agents GROUP BY MONTH( date_of_reg ) ORDER BY year, monthno

But I didn't achieve which I want.

Answer 1

SQL

First up, you need to create a table (temporary or otherwise) which represents the last 6 months (tbl_agents may not have any records in it). You can do this using the "union" syntax and the date_sub function (this makes it easy to follow what is happening):

 select now() as month union 
 select date_sub(now(), INTERVAL 1 MONTH) as month union 
 select date_sub(now(), INTERVAL 2 MONTH) as month union 
 select date_sub(now(), INTERVAL 3 MONTH) as month union 
 select date_sub(now(), INTERVAL 4 MONTH) as month union 
 select date_sub(now(), INTERVAL 5 MONTH) as month union 
 select date_sub(now(), INTERVAL 6 MONTH) as month 

Output:

month 
2018-01-25T03:04:22Z 
2017-12-25T03:04:22Z 
2017-11-25T03:04:22Z
2017-10-25T03:04:22Z 
2017-09-25T03:04:22Z 
2017-08-25T03:04:22Z
2017-07-25T03:04:22Z

We can then use a LEFT JOIN to tbl_agents on the month and use your existing SQL to achieve the necessary output. Note, we're working from our temporary table above to give the dates:

SELECT MONTH(m.month) AS monthno,
       MONTHNAME(m.month) as month_name,
       YEAR(m.month) as year,
       SUM(CASE WHEN `free_user` = 'y' THEN 1 ELSE 0 END) AS free_user,
       SUM(CASE WHEN `free_user` = 'n' THEN 1 ELSE 0 END) AS paid, 
       SUM(CASE WHEN `free_user` != '' THEN 1 ELSE 0 END) as total_users
FROM (
     select now() as month union 
     select date_sub(now(), INTERVAL 1 MONTH) as month union 
     select date_sub(now(), INTERVAL 2 MONTH) as month union 
     select date_sub(now(), INTERVAL 3 MONTH) as month union 
     select date_sub(now(), INTERVAL 4 MONTH) as month union 
     select date_sub(now(), INTERVAL 5 MONTH) as month union 
     select date_sub(now(), INTERVAL 6 MONTH) as month 
  ) AS m
LEFT JOIN tbl_agents as tb
   ON MONTH(tb.DATE_OF_REG) = MONTH(m.month)
GROUP BY MONTH(tb.DATE_OF_REG)
ORDER BY m.month DESC;

Output:

| monthno | month_name | year | free_user | paid | total_users |
|---------|------------|------|-----------|------|-------------|
|       1 |    January | 2018 |         1 |    0 |           1 |
|      12 |   December | 2017 |         1 |    1 |           2 |
|      11 |   November | 2017 |         1 |    0 |           1 |
|      10 |    October | 2017 |         0 |    0 |           0 |
|       9 |  September | 2017 |         0 |    1 |           1 |
|       8 |     August | 2017 |         1 |    0 |           1 |
|       7 |       July | 2017 |         0 |    1 |           1 |

You can see this in action in this fiddle: http://sqlfiddle.com/#!9/5ad682/12/0

PHP

The output required in your example is JSON - all you therefore need to do is grab the data from MySQL and build up an array representing the output, before converting to JSON. For example, it may look something like:

<?php

//$data = mysql_query_results

$output_array = array('month_of_registration'              => array(), 
                      'free_users_count_for_related_month' => array(), 
                      'paid_users_count_for_related_month' => array());

foreach( $data as $e ) {
   $output_array['month_of_registration'][] = $e['month_name'];
   $output_array['free_users_count_for_related_month'][] = $e['free_user'];
   $output_array['paid_users_count_for_related_month'][] = $e['paid'];
}

$output_data = json_encode($output_array);

Answer 2

You need to generate 6 rows for the months because there may be gaps in the registrations, and you would need to decide when this starts from, I have used the current date below as that point but it could be any wanted date. Note I have worked backwards from that date.

This subquery seeds the dates with the beginning of the current month, and the first day of the next month:

set @datum := DATE_FORMAT(current_date, '%Y-%m-01');
...
cross join (select @datum dt_fm, @datum + interval 1 month dt_to) d

Then when joined to 6 rows of numbers (0 to 5) we calculate date ranges over the last 6 months, and they in turn allow us to place to the registrations into those monthly buckets for counting.

set @datum := DATE_FORMAT(current_date, '%Y-%m-01');

select
       monthname(dt_fm - interval n.n month) mnth
     , dt_fm - interval n.n month dt_fm
     , dt_to - interval n.n month dt_to
     , count(case when t.free_user = 'y' then 1 end) free_users
     , count(case when t.free_user = 'n' then 1 end) paid_users
from (
    select 0 n union all
    select 1 n union all
    select 2 n union all
    select 3 n union all
    select 4 n union all
    select 5 n
    ) n
cross join (select @datum dt_fm, @datum + interval 1 month dt_to) d
left join tbl_agents t on t.DATE_OF_REG >= dt_fm - interval n.n month
                      and t.DATE_OF_REG <  dt_to - interval n.n month
group by
       monthname(dt_fm - interval n.n month)
     , dt_fm - interval n.n month
     , dt_to - interval n.n month
order by dt_fm
;

Results:

|      mnth |      dt_fm |      dt_to | free_users | paid_users |
|-----------|------------|------------|------------|------------|
|    August | 2017-08-01 | 2017-09-01 |          1 |          0 |
| September | 2017-09-01 | 2017-10-01 |          0 |          1 |
|   October | 2017-10-01 | 2017-11-01 |          0 |          0 |
|  November | 2017-11-01 | 2017-12-01 |          1 |          0 |
|  December | 2017-12-01 | 2018-01-01 |          1 |          1 |
|   January | 2018-01-01 | 2018-02-01 |          1 |          0 |

Note that the count() function ignores null so the case expressions do not use an else so when the expression isn't met null is evaluaed and the count does not increment. Alternatively you could use else null in the case expressions.

I am going to assume that once you get that sql result in an array you can then work that into the inputs for your chart.

Demo at sqlfiddle