0

I've got the following query on a timestamp(specified by INT(10), which kind of does what I want, but not exactly:

SELECT count(entry_date) as theCount, FROM_UNIXTIME(entry_date, '%Y-%m-%d') AS dd 
FROM exp_weblog_titles 
WHERE entry_date < UNIX_TIMESTAMP(NOW()) 
GROUP BY dd 
ORDER BY dd DESC 
LIMIT 7

That spits out the last 7 dates and the # of entries on those dates that are before today's date. The problem with the query I've created is, it only spits out the date if it actually has an entry on it. When in actuality, I still want that date included even if there are zero entries. Is that possible?

Dan J
  • 16,319
  • 7
  • 50
  • 82
Macgyver
  • 1,831
  • 2
  • 16
  • 19

1 Answers1

4

You will need to have a list of dates from somewhere. A lot of people create a calendar table just for this purpose with every date (or every value) that they need so that this calendar table and your data table can be joined to provide this kind of query.

You will find in the long run this is far simpler than any other solution, just takes a moment to set up.

See this article for an example of how to generate such a table.

Henry
  • 6,502
  • 2
  • 24
  • 30