PHP MYSQL logic for get Grouped Data

Question

I have MYSQL timestamp column called delivery_start_time Which have value like 2021-12-04 05:34:16.

I am looking for show grouped data in bar chart from delivery_start_time to next 7 days . My Query is like below. I have made query like below for get data.

SELECT COUNT(link_data_id) as total_click, link_data_time FROM tbl_links_data GROUP BY CAST(link_data_time AS DATE)

its working fine but giving me data of the dates which have values, What I am looking is get data for the dates even which have no records, I am learning PHP and MYSQL, I am not getting idea How I can get data like below in my javascript so My chart can get dynamic data.

var data = google.visualization.arrayToDataTable([
                ['Day', 'Clicks'],
                ['04-12-2021',  50],
                ['05-12-2021',  100],
                ['06-12-2021',  120],
                ['07-12-2021',  80],
                ['08-12-2021',  130],
                ['09-12-2021',  100],
                ['10-12-2021',  70]
            ]);

Let me know if anyone here can help me for achieve my goal. Thanks!

PHP MYSQL logic for get Grouped Data

0 Answers0