在特定时间跨度之间按“某个时间间隔”获取日期组数组

Question

I am new to MySql . I am trying to get some data from a web server of e-commerce website by placing a php file on it. I want to get only the count of all products sold or cancelled everyday. If no product is sold, It should return 0. and I want to get details in particular time span, like every week, month or year. And I want to get the data in array. Like the sales in week is:

3,7,9,0,6,0,1

or with any separator. I have some part of working query but not all:

SELECT count(*)
FROM `ad_aff`
WHERE STATUS ='rejected'
AND datetime BETWEEN '2013-07-23' 
AND DATE_ADD('2013-07-23',INTERVAL 2 MONTH)

PS: I want to get 60 results from this query.. with 0's on all places where no product is rejected

Answer 1

Use GROUP BY to get the count for each date.

SELECT DATE(datetime) date_rejected, COUNT(*) ct
FROM ad_aff
WHERE STATUS = 'rejected'
AND datetime BETWEEN '2013-07-23' AND DATE_ADD('2013-07-23',INTERVAL 2 MONTH)
GROUP BY date_rejected
ORDER BY date_rejected

You can put them in an array in the PHP application that reads the results.