title: How can I get MySQL records from past 7 days, but only days that have a record, and return each day as a record?
body: <p>Say I've got a simple mysql table with columns id, title, date. Each day can have several rows, but not <em>every</em> day has a row. For example there might be 5 rows with June 15th as the date, but zero rows with June 14th as the date. How can I get all results from the past 7 days, excluding June 14th because it has no rows. So I'm not just subtracting 7 days from the current date. I want to get only the past 7 days which have any rows at all.</p>
  
  <p>Then I want to return each day as a single result, like a group by date, but by the year/month/day part of the timestamp, so that what I get back is only 7 results, and each result is like an array of individual rows.</p>
  
  <p>I hope that makes sense. Any ideas?</p>
  
  <p>Edit:
  I realized I can do it something like this:</p>
  
  <p><code>WHERE Date BETWEEN DATE_SUB( NOW(), INTERVAL DATEDIFF( NOW(), (SELECT Date FROM test GROUP BY Date LIMIT 7,1 ) ) DAY ) and NOW()</code></p>
  
  <p>But this gives an SQL syntax error. What I'm trying to do is a subquery and group by date to get one result for each day, and return one result starting at offset 7, then do a <code>DATEDIFF</code> on that to get the number of days that <code>DATE_SUB</code> should put in the <code>INTERVAL x DAYS</code></p>