[Chartjs]-Daily activity chart using PHP MySQL

by

1👍

✅

Use DB query like this for your solution (and correct it for your needs):

Select count(post_id) as post_cnt, w.name as author, date_posted 
from posts as p 
join writers w on p.user_id = w.id 
where p.user_id = #user_id 
group by date_posted;

For PHP use some framework with caching, and send data with json format to ajax respond, to your JQuery plugin, I use Google Chart like this, but I load it if user realy need it (like click, or page scroll event etc.)

0👍

Thanks to @Jan Sršeň. As he suggest I use the query below: 

$query = sprintf("SELECT count(`p`.`user_id`) as `post_cnt`, `w`.`id` as `author`,date_posted from posts as p join writer w on `p`.`user_id` =  `w`.`id` where `p`.`user_id` = '$user_id' group by `date_posted`");

and generate a json file:

[{"post_cnt":"3","author":"1001","date_posted":"2017-01-08 14:26:14"},{"post_cnt":"1","author":"1001","date_posted":"2017-01-12 08:37:55"},{"post_cnt":"4","author":"1001","date_posted":"2017-01-12 23:27:23"},{"post_cnt":"1","author":"1001","date_posted":"2017-01-12 23:29:15"},{"post_cnt":"2","author":"1001","date_posted":"2017-01-12 23:30:27"},{"post_cnt":"1","author":"1001","date_posted":"2017-01-12 23:31:04"},{"post_cnt":"1","author":"1001","date_posted":"2017-01-12 23:33:03"}]

I, then, used these data in ChartJS to generate a Line Chart!

Thank you!

Leave a comment