bambinou1980 Posted August 17, 2015 Share Posted August 17, 2015 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 Quote Link to comment https://forums.phpfreaks.com/topic/297832-output-the-last-12-months-sales-in-an-array-php-mysql/ Share on other sites More sharing options...
Barand Posted August 17, 2015 Share Posted August 17, 2015 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 Quote Link to comment https://forums.phpfreaks.com/topic/297832-output-the-last-12-months-sales-in-an-array-php-mysql/#findComment-1519094 Share on other sites More sharing options...
bambinou1980 Posted August 17, 2015 Author Share Posted August 17, 2015 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 Quote Link to comment https://forums.phpfreaks.com/topic/297832-output-the-last-12-months-sales-in-an-array-php-mysql/#findComment-1519101 Share on other sites More sharing options...
Barand Posted August 17, 2015 Share Posted August 17, 2015 MONTH(date) Returns the month for date, in the range 1 to 12 for January to December, Quote Link to comment https://forums.phpfreaks.com/topic/297832-output-the-last-12-months-sales-in-an-array-php-mysql/#findComment-1519104 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.