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.

I written a 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 didn't achieve what I actually want. — Cody
– Cody, Commented Jan 25, 2018 at 1:29

Paul Maxwell · Accepted Answer · 2018-01-25 02:56:58Z

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

stefancarlton · Accepted Answer · 2018-01-25 03:06:32Z

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

answered Jan 25, 2018 at 3:06

stefancarlton

1,72711 silver badges12 bronze badges

3 Comments

Cody Over a year ago

Thanks for your solution. I just want to know what to do if I want to increase number of months from 6 to 12.

Paul Maxwell Over a year ago

Currently this query returns 7 rows but will scale only up to 12 rows as it uses the month function for joining to the data (which isn't efficient in sql). To go to 12 rows just keep adding more rows to the subquery m so the last line would be select date_sub(now(), INTERVAL 11 MONTH) as month. To scale beyond 12 rows requires using the function year () in addition to month for joining. Using date ranges is a more efficient method of joining the month rows to the data as it allows sql to use an index on the date_of_reg column. Using functions in joins should be avoided.

stefancarlton Over a year ago

Used_By_Already's is absolutely correct - month/year etc are not efficient in he slightest. They are however easier to understand in concept if you're learning SQL concepts :). You can also use a pre populated calendar table too - this would simply be a table populated with date ranges for each month, the name etc

Collectives™ on Stack Overflow

MySql query to display data in Chartjs

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related