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
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.