Possible Duplicate:
What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?
Every "record" created in some_table has a "date".
some_table:
-------------------------
id | record | my_date
-------------------------
1 | rec_a | 2012-08-01
2 | rec_b | 2012-08-02
3 | rec_c | 2012-08-02
4 | rec_d | 2012-08-04
5 | rec_e | 2012-08-06
6 | rec_f | 2012-08-06
my_query:
start_date and end_date come from an input
SELECT
my_date, COUNT(my_date) AS _count
FROM
some_table
WHERE
my_date
BETWEEN
start_date
AND
end_date
GROUP BY
DAY (my_date)
array:
while($result = mysql_fetch_assoc($my_query))
{
$dataset[] = array($result['my_date'],intval($result['_count']));
}
json_encode($dataset);
this will make:
["2012-08-01",1]
["2012-08-02",2]
["2012-08-04",4]
["2012-08-06",6]
what is the best way to get the days where there are no counts like this?
["2012-08-01",1]
["2012-08-02",2]
["2012-08-03",0] <--
["2012-08-04",4]
["2012-08-05",0] <--
["2012-08-06",6]
Probably the easiest way to do this is to create a table with all possible dates in it. You would then select from that table and left join to your existing table.
It might seem hacky, but this is a very common pattern, especially in data warehousing.