On 8 May, 12:37, strawberry <zac.ca...@gmail.com> wrote:
> 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 athttp://datacharmer.blogspot.com/2006/06/filling-test-tables-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.

"See his site" (not "sight")!

Actually I rather like one of the other approaches that was suggested.
Have a table containing the correct number of rows for the amount of
dates you want in you notional calendar table. You can then create the
dates on the fly by querying this table.