title: MySQL how to fill missing hours/dates in range?
body: <p>I'm trying to extract data according to Day of Week &amp; also Hour of day in the same statement (so I can see how many visits I got per hour and over a week. Here's the statement.</p>
  
  <pre><code>SELECT
  count(id) as count,
  HOUR(created) as hour_of_day,
  WEEKDAY(created) as day_of_week,
  DATE_FORMAT(created,'%W') name_of_day
  FROM visitors
  GROUP BY day_of_week,hour_of_day
  ORDER BY day_of_week,hour_of_day ASC
  </code></pre>
  
  <p>Some sample data</p>
  
  <pre><code>    count   hour_of_day day_of_week name_of_day
      2       0           0           Monday
      1       1           0           Monday
      1       4           0           Monday
      4       5           0           Monday
      1       6           0           Monday
      4       7           0           Monday
      1       9           0           Monday
      1       10          0           Monday
      1       12          0           Monday
      1       13          0           Monday
      2       16          0           Monday
      5       18          0           Monday
      5       19          0           Monday
  </code></pre>
  
  <p><strong>The problem</strong>
  As you can see, there data missing for many hours in the data. And as I'm creating a graph which needs data in the form of [x,x,x,x,x,x,x] for each day which will be matched with a 24hr timeline starting from the first output, I need the missing ones to be '0'.</p>
  
  <p>While I can handle it on the PHP end with loops, looping it for each day of the week &amp; within that, for every hour, is rather tiresome and definitely not clean.</p>
  
  <p>Is it possible without temporarily tables (like including the 24 digits in the query itself,etc?)?</p>