MySQL GROUP BY date - how to return results when no rows

I am writing a query 开发者_如何学Goto return the number of blog posts written per day over a certain period of time. My problem arises when there are no records of a blog for a given day. With my query, the result for that day is simply skipped altogether.

Here's my query:

SELECT DATE(`posted`), COUNT(`id`) 
    FROM `blogs` WHERE `status` = 'active' 
    && `posted` BETWEEN `2011-01-01` AND `2011-05-01` 
    GROUP BY DATE(`posted`)

It returns something similar to:

count | date
_________________
2     |  2011-01-01
5     |  2011-01-02
1     |  2011-01-04

Notice that it is missing 2011-01-03 because it doesn't have any posts.

How do I get it to show those days with 0 posts?

You'd need to have a table that contains all the dates you're going to query over, and do something along the lines of...

SELECT DATE(D.`thedate`), COUNT(`id`)
FROM `datetable` D
LEFT JOIN `blogs` B
ON B.`posted` = D.`thedate`
WHERE `status` = 'active'     
&& D.`thedate` BETWEEN `2011-01-01` AND `2011-05-01`     
GROUP BY DATE(D.`thedate`)

To create the table containing the dates (taken from http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#95) :

-- Create a dummy view with 3 rows
create or replace view v3 as select 1 n union all select 1 union all select 1; 
-- create a second dummy view with 10 rows
-- By making joins with this view, you can create 100, 1000, ... rows
create or replace view v as select 1 n from v3 a, v3 b union all select 1; 
-- counter
set @n = 0; 
-- create date table
drop table if exists datetable; 
create table datetable(thedate date primary key); 
-- populate from start date
insert into datetable select cast('1970-1-1' + interval @n:=@n+1 day as date) as thedate 
from v a, v b, v c, v d, v e, v;