MySQL query to get non-data for unassigned dates

Question

I have a table with weekly data that looks like:

userID       Site             date
------      ------           ------
Smith    Ferris Wheel       2009-07-13
Jones   Outerspaceland      2009-07-13
LChar   Ferris Wheel       2009-07-14
Smith     Underworld        2009-07-16
Jones     Fish Bowl         2009-07-17
Munson    Go-Go Tech        2009-07-16

Currently I have a PHP script that first gets the date range (Monday -- Friday), then does a query for each userID for that date range. Then, I loop through the results and check for gaps between dates. If there is gap, it outputs the date and the Site is listed as "Stay Home." It has to account for several days in a row (like if the user is only stationed on Monday and Friday that week), and it has to an extra step at the end of each week to make sure that if the user is staying home after Wednesday (thus having Thursday and Friday to fill in as gaps). So there are three checks it has to make, one for users not starting on Monday, one for gaps between days, and one for users ending before Friday...plus it has to account for days in a row for any of those situations.

The script basically creates an array of the date range to check against, and it has worked fine for months. HOWEVER

I was wondering, is there a MySQL function to have days off returned for each user? So that I can have it fill in "Stay Home" in the query and not have to run a funky script that takes forever to explain to other people working with me on my script?

Thanks!

Answer 1

Easy! Flip the query around!

That is:

1. Create a lookup table with the days of the week
2. Sculpt a query that performs a LEFT_OUTER_JOIN on the lookup table and your table DAYNAME(field). That is, it will return all rows from the lookup table and only those that match in your table,,,

I'm not sure if you want to filter by person .. the criteria would look like userid = 'person' or userid is null

The resulting data will look be ...

1. A ROW for ALL DAYS
2. In the row, UserIDs will exist for Non Blank Days and be null for days skipped