Output the last 12 months sales in an array php mysql

[bambinou1980]

Hello,

 

I have created the below query to get all the totals of sales during a chosen date from 3 date pickers.

My question now is how to get 12 months of results when I choosen a year but have each months output it's total sales.

The reaosn I need this is because I need to have an array showing every month  + total sales per month so I can json_encode() it and pass it into a graph using Morris.js(Graph library) as shown here:

<?php
$query = "SELECT SUM(cust_order_total) AS order_total_sum FROM orders WHERE due_date BETWEEN '2015-08-14' AND '2015-10-14'";
$result = mysqli_query($connection, $query);
while ($row = mysqli_fetch_array($result)){
echo $row['order_total_sum'];//This return totals of sales during chosen period.
    }
?>

This currently output well the total sales during the chosen period, so it works. Now I need the exact same type of logic but When I click on the dropdown menu that choses a Year, I would like to see:

Year 2014

 

Month

01  ->  €239

02  -> €250

03  -> €456

 

And so on....

 

Any idea please?

Remember I am saving the date as yyyy-mm-dd in mysql but read it as dd-mm-yyyy on the web page.

 

Thank you,

 

Ben

[Barand]

the query would be

SELECT 
    MONTH(due_date) as month
  , SUM(cust_order_total) AS order_total_sum 
FROM orders 
WHERE YEAR(due_date) = 2014
GROUP BY month

[bambinou1980]

Hi Barand,

 

Thank you for the  fast reply.

I am not understanding why you add "MONTH(due_date) as month" does this function automatically recognise the /mm/  in the date itself?

 

Thank you,

 

Ben

[Barand]

 

MONTH(date)

Returns the month for date, in the range 1 to 12 for January to December,