Im trying to graph the sum of orders occuring for a particular date for the last 7 days but am having a problem when their aren't any orders for a particular date as the query isn't a value nor a date and therefore returning an array less than 7.
SELECT IFNULL(COUNT(*),0) as purchase_count, DATE(tb_order.order_date) as order_date
FROM tb_order_attendee_info
JOIN tb_order on tb_order.order_id = tb_order_attendee_info.order_id
JOIN tb_events on tb_events.event_id = tb_order.event_id
WHERE tb_order_attendee_info.refunded='N' AND order_date between DATE_SUB(now(), interval 10 day) and now()
GROUP BY DATE(tb_order.order_date)";
Ive been trying to work out how i can get it to return the array with zero values for purchase count and the incremented date, unfortunately its only returning a date when the count is greater than 0.
解决方案
You are trying to introduce result rows where there are none in the data. This is just not possible (without using a Stored Procedure). You cannot iterate over the dates as, so to say, there are no for loops in SQL.
The closest you can get is having/creating a (temporary) table of all the dates you want to have in the result and then JOIN to this table.