MySQL select all days in date range even if no data is available for day

Question

I have entries for each days as a punch in/out clock. I would like to display all days for the date givens week and show the days even if no data in found for that day.

Example:

Monday 2015-05-04
- 2015-05-04 10:30:00
- 2015-05-04 15:45:34

Tuesday 2015-05-05
- 2015-05-05 08:43:23
- 2015-05-05 17:18:13

Wednesday 2015-05-06
- 2015-05-06 09:03:12

Thursday 2015-05-07
0 Entries

Friday 2015-05-08
0 Entries

The DB schema looks like:

id | user_id | punch_time | punch_status

I would ideally like to be able to change the date given to MySQL to any timestamp and it would show the days and results for that week.

Thanks :D

---

NEW Any ideas why this doesn't work to get days when no records are present?

SELECT * FROM punch_clock, calendar_table WHERE calendar_table.dt = DATE(punch_clock.punch_time) && calendar_table.week_num = $week && calendar_table.y = $year ORDER BY punch_clock.punch_time

Newer Query

 SELECT * FROM punch_clock LEFT JOIN calendar_table ON calendar_table.dt = DATE(punch_clock.punch_time) WHERE calendar_table.week_num = 18 && calendar_table.y = 2015;

Answer 1

With MySQL, I usually use a calendar table for this purpose (containing all the dates until 2030 for example)
It allows to do many other things like this kind of queries, manage special days, etc..

You'll want to LEFT JOIN your table on it, I mean this calendar table have to be "left positioned"

Taking your last query, I'd do this:

SELECT *
FROM calendar AS cal
LEFT JOIN punch_clock AS puc
  ON (cal.dt = DATE(puc.punch_time))
WHERE TRUE
  AND cal.week_num = 18
  AND cal.y = 2015
;

Didn't try, but this is the idea.