Grouping records by day, including days between them that do not contain records in MySQL

Question

I have to show how many votes were issued each day (from the first vote to the last vote), users voted for the poll 2 .

My current request is working fine; but I have problems when there are no votes on a particular day. For example, for poll 2, this should be the result of:

• May 11, 2017 = 1
• May 12, 2017 = 0
• May 13, 2017 = 0
• May 14, 2017 = 0
• May 15, 2017 = 0
• May 16, 2017 = 0
• May 17, 2017 = 1
• May 18, 2017 = 0
• May 19, 2017 = 0
• May 20, 2017 = 2

... but instead I get the following:

• May 11, 2017 = 1
• May 17, 2017 = 1
• May 20, 2017 = 2

, , , ( ), . :

SELECT DATE(poll_vote.date_insert) AS date_insert,
COUNT(poll_vote.id_vote) AS q
FROM poll_vote WHERE poll_vote.id_poll = 2
GROUP BY DATE(date_insert) ORDER BY date_insert

SQL Fiddle . !

Answer 1

@Strawberry, php mysql. , , .

0 1 , , . :

• 11 2017 = 1
• 16 2017 = 0
• 17 2017 = 1
• 19 2017 = 0
• 20 2017 = 2

, :

$sql = 'SELECT DATE(poll_vote.date_insert) AS date_insert, COUNT(poll_vote.id_vote) AS q FROM poll_vote WHERE poll_vote.id_poll = :id_poll GROUP BY DATE(date_insert) ORDER BY date_insert';

$stmt = cnn()->prepare($sql);
if($id_poll) $stmt->bindValue(':id_poll', $id_poll, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll();

# insert blank dates between existing dates
$hotness = array();
foreach($data as $item) {
    if(!$temp) {
        $temp = $item['date_insert'];
    } else {
        $date_past = new DateTime($temp);
        $date_now = new DateTime($item['date_insert']);
        $diff = $date_now->diff($date_past)->format("%a");
        if($diff > 1) {
            $date_new = new DateTime($item['date_insert']);
            $date_new->modify('-1 day');
            $hotness[] = array(
                'date_insert' => $date_new->format('Y-m-d'),
                'q' => 0
            );
        }
    }
    $hotness[] = array(
        'date_insert' => $item['date_insert'],
        'q' => $item['q']
    );
}

# final result
print_r($hotness);