On 7 May, 17:32, Mo <Mehile.Orl...@gmail.com> wrote:
> After a little PHP education, my first project (to get my feet wet) is
> to
> make an employee time-tracking program for our small business.
>
> *** ANY SUGGESTION, THOUGHTS, OR ADVICE WOULD BE WARMLY WELCOMED ***
>
> I'm in my planning stage, and here's my hurdle:
> On the time report, we want to see EVERY date under each employee,
> and
> the string "Absent" for the dates on which the user has no punch
> times. (It would also be nice to include day-names on the report.)
> Obviously the Time table will only have entries for when people
> actually clock in/out, so how do I get the other dates to show on the
> report?
> Any suggestions?
>
> One guy here suggested just adding a table *which includes every date
> from here to ...., then just do an outer join.
> This seems like it could work well, but I would like to get some input
> from those with more experience.
>
> Lastly, if this Calendar table is a good way to go, does anyone have
> any
> advice on how to go about creating one (preferrably with day and date
> values), *or a table like this wich they coud just provide a SQL
> export of.
>
> While a table dump would get me going quicker, I'd prefer to learn how
> to do it myself.
> I would then be able to create a form enabling me to periodically
> (probably at the turn of our fiscal year) just enter the new ending
> date (or date range) to further populate the Calendar table.
>
> ~Mo
>
> (NOTE: I originally posted this in comp.lang.php, where it was
> suggested that I repost in this group.)

You could use an external scripting language like PHP to create the
calendar dates on-the-fly, and then compare this with data in your
table but, for a purely mysql solution, yes you'd probably need to
create the a calendar table. Fortunately, Giuseppe Maxia has a nifty
little procedure for doing this very quickly. See his sight at
http://datacharmer.blogspot.com/2006...s-quickly.html.
Personally, I'd do as the other respondent suggested and just create a
calendar table along these lines. Note that you don't have to store
days, just dates. Days can be easily calculated using mysql's built-in
date functions.