如何在循环中回显没有结果的日子？

Question

I want to count the number of posts for each day to create a graph. My problem is that since SQL doesn't find results for some days (Count is 0), I'm missing rows I need for the chart (since I do want to show days with no posts).

SELECT DATE(Date) AS Day, COUNT(*) AS COUNT 
FROM `Posts` 
GROUP By `Day`
ORDER BY Date DESC

while($row = mysql_fetch_array($result)) {
    echo $row['Date'] . ": " . $row['Count'];
    }

Since the loop doesn't display days with 0 results, if on wednesday there are no posts I get: monday-17-3: 5, tuesday-18-3: 2, thursday-20-3: 3. Instead I want to fill out the blanks so I get something like: wednesday-19-3: 0.

How can I echo the days with no results in the loop?

Answer 1

You can work around this by a table of dates, performing an OUTER JOIN, and then performing the grouping. This will provide you with the dates in between (Disclaimer: I'm assuming your dates are in the format YYYY-MM-DD, otherwise you may need to tweak the JOIN statement slightly.).

SELECT A.Date AS Day, COUNT(Posts.Date) AS COUNT 
FROM 
   (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) A
LEFT OUTER JOIN `Posts` ON A.Date = `Posts`.`Date`
WHERE A.Date >= DATE_ADD(CURDATE(), INTERVAL -15 DAY)
GROUP BY A.Date

For the date table, I'm using the method from the following post: generate days from date range