How to echo days with no results in while loop?

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?

try to execute query in phpmyadmin for example. Does your query result have wednesday? — s.webbandit, Apr 24 '12 at 04:43
the loop works fine. It's just that I need to add days with no results to the array. I added some more info to the description. — lisovaccaro, Apr 24 '12 at 04:47

score 2 · Accepted Answer · edited May 23 '17 at 12:12

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

It's almost perfect but on days with no results I get 1 result and on days with 1 result I also get 1 result. I'm trying to figure out how to fix it — lisovaccaro, Apr 24 '12 at 05:14
Oops, changed COUNT(*) to Posts.Date or Posts.*. Also the reason that you're getting 1 for everything is likely because of the join and having no matches for A.Date=Post.Date. How is your Date field formatted in your posts table? — David Z., Apr 24 '12 at 05:25

score 0 · Answer 2 · answered Apr 24 '12 at 04:50

0

Use a loop to go through successive dates, using a function like:

$date = strtotime(date("Y-m-d", strtotime($date)) . " +1 day");

For each cycle, apply your query result. Then you'll have all the dates.

answered Apr 24 '12 at 04:50

Smandoli

6,919
3
49
83

How to echo days with no results in while loop?

2 Answers2