On 8 May, 14:04, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

"your notional table"

touché