Mysql get count of rows for each day

Question

My Current query is:

SELECT DISTINCT DATE(vote_timestamp) AS Date, COUNT(*) AS TotalVotes FROM `votes` 
WHERE vote_target_id='83031'
GROUP BY DATE(vote_timestamp) ORDER BY DATE(vote_timestamp) DESC LIMIT 30

(line breaks separated for readability)

Where vote_timestamp is a time for each "vote", Count(*) is the count for that day, and vote_target_id is the specific target of the vote.

Currently, this works for all days in which the target has at least one "vote", but I would like it to also return TotalVotes as 0 for days where there are no votes, rather than having no row at all.

Can this (and how?) be done in MySQL or PHP? (either is fine, as it is futher processed by PHP so either code can be used).

Thank you

Answer 1

The problem is how to generate records for days that have no rows. This SO question has some approaches.

Answer 2

Looking at that solution, it looks like for me it's much simpler to do this quick fix that sorta works.

$result = mysql_query($sql) or die('Internal Database Error');
if (mysql_num_rows($result) == 0) { return false; }
while($row = mysql_fetch_assoc( $result )) {
 $votes[$row['Date']] = $row['TotalVotes'];
}
// fill 0s with php rather than using mysql
$dates = array_keys($votes);
for ($t = strtotime($dates[count($dates)-1]); $t <= time(); $t +=86400) {
 $date = date('Y',$t).'-'.date('m',$t).'-'.date('d',$t);
 if (!array_key_exists($date,$votes)) {
  $votes[$date] = 0;
 }
}

thanks though,