title: MYSQL: Count entries in a "event" table, grouping by date and row count - for a date period
body: <p>I have this query to count "views" in a table, by date. It obviously gives me only the dates where there has been activity, but I wish I could have MySQL return data for a selected period of time. For example 30 rows for a 30 day period, creating null data for dates where there are no matching rows.</p>
  
  <p>I wish to avoid using PHP to figure this out, so I thought I'd ask here for advice on how to maybe accomplish this with a smart query.</p>
  
  <p>QUERY:</p>
  
  <pre><code>SELECT count(ID) AS views, date AS view_date
  FROM logging.views 
  WHERE date &lt; DATE_SUB(NOW(),INTERVAL 30 DAY) 
  GROUP BY DAY(date)";
  </code></pre>
  
  <p>Example below only gives me 7 rows, obviously because there are only entries matching 7 different days in the selected 30 DAY interval. I wish I could get 30 rows representing all days in the set interval.</p>
  
  <p>OUTPUT:</p>
  
  <pre><code>Array
  (
      [0] =&gt; Array
          (
              [date] =&gt; 2010-09-01 22:04:19
              [views] =&gt; 20
          )
  
      [1] =&gt; Array
          (
              [date] =&gt; 2010-08-13 00:15:54
              [views] =&gt; 4
          )
  
      [2] =&gt; Array
          (
              [date] =&gt; 2010-08-24 17:46:53
              [views] =&gt; 2
          )
  
      [3] =&gt; Array
          (
              [date] =&gt; 2010-08-25 07:18:11
              [views] =&gt; 35
          )
  
      [4] =&gt; Array
          (
              [date] =&gt; 2010-08-26 08:02:47
              [views] =&gt; 12
          )
  
      [5] =&gt; Array
          (
              [date] =&gt; 2010-08-27 09:56:05
              [views] =&gt; 29
          )
  
      [6] =&gt; Array
          (
              [date] =&gt; 2010-08-28 12:46:17
              [views] =&gt; 30
          )
  
  )
  </code></pre>