mysql - Show all dates between, even if no result

Question

SELECT User_JoinDate, 
COUNT(User_ID) 
FROM Users WHERE `User_JoinDate` 
BETWEEN '2012-11-22' AND '2012-12-06' 
GROUP BY User_JoinDate 
ORDER BY User_JoinDate ASC"

I'm generating data to be displayed in a line graph. Unfortunately I can't figure out how to get the above query to display 0 for a date that no users might have registered. So currently my output might be something like this:

2012-11-22 - 2
2012-11-25 - 4

But what I want is

2012-11-22 - 2
2012-11-23 - 0
2012-11-24 - 0
2012-11-25 - 4

I do currently have a working version which stores the MySQL result into an array and then PHP loops through and fills the blanks. This seems very messy and I was hoping there would be a solution through MySQL. I have had a good search of the site but struggling to understand some of the implementations. Any suggestions?

Answer 1

You can build out an automated result set using MySQL variables for all the dates you want.

select
      AllDaysYouWant.MyJoinDate,
      count( U.User_ID ) as NumberJoined
   from
      ( select
              @curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate
           from
              ( select @curDate := '2012-11-21' ) sqlvars,
              Users
           limit 18 ) AllDaysYouWant
      LEFT JOIN Users U
         on AllDaysYouWant.MyJoinDate = U.User_JoinDate
   group by
      AllDaysYouWant.MyJoinDate

The inner query, I am just joining to the users table with no key, so its just used to cycle through X number of records to represent the day span you want... This could be 30, 100, whatever.... Just as long a the table (in this case users), has as many records as you are expecting.

THEN, that result of nothing but days is joined to the users table, but this time, based on the JOIN_DATE of the user. The simple COUNT() should get you what you want.

The "AllDaysYouWant" is the alias assigned to the internal first part query of

  ( select
          @curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate
       from
          ( select @curDate := '2012-11-21' ) sqlvars,
          Users
       limit 18 ) AllDaysYouWant

This basically states... From the users table (but could be any), give me 18 rows of data (via limit, but could be almost any number of records, but you only need from Nov 22 to Dec 6, which is only 14 days, but I did 18 just for principle it could be almost anything. Above the Users table is (select @curDate := '2012-11-21' ) sqlvars. Any select statement within a query that is wrapped in parentheses as a table source must be given an alias and since it is just a variable I'll be using, don't care what its name is. So, this query starts the variable at Nov 21 and the Select @curDate := Date_Add...blah blah states to take the current value of @curDate, add 1 day to it (now becomes Nov 22) and store it in the returned row "MyJoinDate". So now, this inner query creates your table of just dates going from Nov 22 forward 18 days worth of data and has the alias "AllDaysYouWant" for the rest of the query to reference.

I've adjusted the query which was probably what you encountered, to alias.field everything for clarification...