php - Dynamically creating date periods using MySQL

Question

I trying to get the Grape count from dates March 1 - 3.

You will notice that on March 2 - there are no grapes inserted..

I'st possible to show a query from dates March 1, 2 and 3 but showing 0 count for March 2

In this image above only shows dates where there are grapes..

Here is mySQL query

SELECT  `fruitDate` ,  `fruitName` , COUNT( * ) 
FROM  `tbl_fruits` 
WHERE  `fruitName` =  "Grapes"
GROUP BY  `fruitDate

UPDATE 2:

Using this query:

SELECT f.fruitDate, f.fruitName, f1.count FROM tbl_fruits f
    LEFT JOIN (SELECT fruitDate, COUNT(*) as count from tbl_fruits d WHERE d.fruitName='Grapes' GROUP BY d.fruitDate) as f1 ON (f.fruitDate = f1.fruitDate) 
    GROUP BY f.fruitDate

I got this result..but its dsplaying diffrent fruit..something wrong with my query?

Answer 1

March 2 is data that does not exist in your table, what would it select from? That is to say, since count() is counting the number of rows that exist for "Grapes" on each date, and no rows exist for "Grapes" on March 2, count() has no data to count, nothing at all to tell the database to interpolate missing dates.

In order to solve this, what I have done in the past is create a separate table, Calendar, that contains a row for each date for a given range. Then, you can JOIN to this table to assure you select a row for each date. It might look something like this:

SELECT cal.`Date`, 'Grapes' as `fruitName`, COUNT(f.`fruitName`)
FROM `tbl_Calendar` cal 
LEFT JOIN `tbl_fruits` f ON cal.`Date` = f.`fruitDate`  
WHERE `fruitName` = "Grapes"
 AND '2012-03-01' <= cal.`Date` AND cal.`Date` <= '2012-03-03' 
GROUP BY cal.`Date`

Note that count(*) would never return 0 because a row would be returned for each date. To get a 0, count a field that would be NULL when 0 rows are found, in this case, I count fruitName

The Calendar table could be as simple as

CREATE TABLE tbl_Calendar (
  `Date` date NOT NULL PRIMARY KEY
)

Which you would fill with a simple PHP loop from a chosen start date to end date. You may find a benefit in adding other columns to cache things like day-of-week or holidays, but that is not needed for this task.

EDIT

In your edit, you seem to be trying to join back to your fruits table to get dates, but you have some errors in your query, try instead to substitute a similar subquery in place of my Calendar table:

SELECT cal.`Date`, 'Grapes' as `fruitName`, COUNT(f.`fruitName`)
FROM (SELECT `fruitDate` as `Date` FROM `tbl_fruits` GROUP BY `fruitDate`) cal 
LEFT JOIN `tbl_fruits` f ON cal.`Date` = f.`fruitDate`  
WHERE `fruitName` = "Grapes"
GROUP BY cal.`Date`

Note, though, while this will fill in dates missing for Grapes that are not missing for some other fruits, it will not fill in dates which are missing for all fruits.