Create PHP arrays with dates and number of orders extracted by month from SQL

Question

I have a table called 'orders' with multiple rows with different dates.

I want to build a graph with number of orders per month, so I need to query an array that looks like this:

• Jan ------- 3
• Feb ------- 1
• Mar ------- 0
• Apr ------- 8 ......

As seen, even when there are no orders, the Month is showing.

From there, I would need to load the amounts of the array into something like this:

$data1y=array(3,1,0,8...);

While I will have an array of months like this:

$graph->xaxis->SetTickLabels(array('Jan','Feb','March','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'));

I tried with a SQL like this one, but no luck:

SELECT COUNT(*) FROM orders GROUP BY month(date)

Answer 1

Your query should something like this:

SELECT DATE_FORMAT(`date`, '%Y%m') AS `Ym`, COUNT(*) AS `count`
FROM `orders`
GROUP BY `Ym`

demo

p.s. You can fill missing months in PHP, or in SQL like this.