How do I query mysql databse to show data for each months in the previous years and current year?

Question

I am working with line chart from amchats demo and I need to get data for each month from previous years and also that of this year from a field in my database. I've tried some queries like getting values for each month in a year by iterating through the months.

But, that's not what I need.

for ($i=12; $i > 0 ; $i--) {
$query = $conn->prepare("SELECT date_inserted FROM communications WHERE YEAR(date_inserted) = YEAR(CURRENT_DATE - INTERVAL $i MONTH) AND MONTH(date_inserted) = MONTH(CURRENT_DATE - INTERVAL $i MONTH)");
}

Btw, I need to get something like: the table below where value is the total count of data found in that month

2012-01-31, Value: 20
2012-02-28, Value : 53
...
2019-04-16, value : 32

You prepare your statements, and yet you still do not bind your parameters... — Dharman, Apr 16 '19 at 11:57
Not sure about PHP, But can be done in mysql. Reference: https://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year — Vijay Sali, Apr 16 '19 at 12:03
Not recommended to execute 12 queries from DB, better to fetch the data and then process it in PHP. Or write your query smartly as mentioned above by @VijaySali — Danish Hakim Khan, Apr 16 '19 at 12:04

score 0 · Accepted Answer · answered Apr 16 '19 at 12:09

0

you can use this query.

 select date_inserted, count(*) as value from communications GROUP BY YEAR(date_inserted), MONTH(date_inserted);

answered Apr 16 '19 at 12:09

Şafak Çıplak

889
6
12

I'm so glad happy coding – Şafak Çıplak Apr 16 '19 at 14:54

How do I query mysql databse to show data for each months in the previous years and current year?

1 Answers1