Novice looking for advice (date comparison).

After a little PHP education, my first project (to get my feet wet) is
making 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 (to the MySQL DB ) 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 a review
from those with more experience.

Lastly, if this Calendar table is a good way to go, does anyone have a
table like this wich they coud just provide a SQL export of, or any
advice on how to go about creating one (preferrably with day and date
values).

~Mo

(NOTE: While a table dump would get me going quicker, I'd prefer to
learn how to do it.)

On Wed, 07 May 2008 17:54:41 +0200, Mo <Mehile.Orloff@gmail.com> wrote:

> After a little PHP education, my first project (to get my feet wet) is
> making 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 (to the MySQL DB ) 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 a review
> from those with more experience.
>
> Lastly, if this Calendar table is a good way to go, does anyone have a
> table like this wich they coud just provide a SQL export of, or any
> advice on how to go about creating one (preferrably with day and date
> values).

It would be the way to go (and the answer is better asked in
comp.databases.mysql), but a 'calender' table with just the numbers from
1-366 for days of the year, and left joining against that would suffice.
You don't have to have _all_ possible dates (which will probably end up
causing some other developer headaches as soon as the dates 'run out'.
--
Rik Wasmus

On May 7, 8:09*am, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Wed, 07 May 2008 17:54:41 +0200, Mo <Mehile.Orl...@gmail.com> wrote:
> > After a little PHP education, my first project (to get my feet wet) is
> > making 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 (to the MySQL DB ) 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 a review
> > from those with more experience.
>
> > Lastly, if this Calendar table is a good way to go, does anyone have a
> > table like this wich they coud just provide a SQL export of, *or any
> > advice on how to go about creating one (preferrably with day and date
> > values).
>
> It would be the way to go (and the answer is better asked in *
> comp.databases.mysql), but a 'calender' table with just the numbers from *
> 1-366 for days of the year, and left joining against that would suffice. *
> You don't have to have _all_ possible dates (which will probably end up *
> causing some other developer headaches as soon as the dates 'run out'.
> --
> Rik Wasmus- Hide quoted text -
>
> - Show quoted text -

Thank you, I'll post to that group.

In learning how to do it, I was hoping to then create a form in which
I could punch in a date range (or just an ending date) and have some
sql further populate the Calendar table. I could just run that at the
turn of our fiscal year or something.
This kinda seemed like it would help avert disaster.

Thanks again.
~Mo

Mo escribió:
> 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 (to the MySQL DB ) which
> includes every date from here to ...., then just do an outer join.

That's crazy. You'd be overpopulating your DB with dumb data that will
need forever maintenance.

It's way easier than that:

1. Fetch the available DB data into a PHP array
2. In PHP, loop through the range of dates. For each date:
- If there is data, print it
- If not, print 'Absent'

SELECT UNIX_TIMESTAMP(DATE) AS DATE, USER, BLAH
FROM PUNCH_TIMES
WHERE .....



As about looping:

$from = mktime(0, 0, 0, 1, 1, 2008);
$to = time();

for($i=$from; $<=$to; $i=strtotime('+1 day', $i)){
echo date('d/m/Y', $i) . ":\n";
if( isset($punch_data[$employee][$i]) ){
...
}else{
echo "Absent\n";
}
/*...*/
}


I'm missing several steps but I hope you see the idea.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

Álvaro G. Vicario wrote:
> Mo escribió:
>> 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 (to the MySQL DB ) which
>> includes every date from here to ...., then just do an outer join.
>
> That's crazy. You'd be overpopulating your DB with dumb data that will
> need forever maintenance.
>
> It's way easier than that:
>
> 1. Fetch the available DB data into a PHP array
> 2. In PHP, loop through the range of dates. For each date:
> - If there is data, print it
> - If not, print 'Absent'
>
> SELECT UNIX_TIMESTAMP(DATE) AS DATE, USER, BLAH
> FROM PUNCH_TIMES
> WHERE .....
>
>
>
> As about looping:
>
> $from = mktime(0, 0, 0, 1, 1, 2008);
> $to = time();
>
> for($i=$from; $<=$to; $i=strtotime('+1 day', $i)){
> echo date('d/m/Y', $i) . ":\n";
> if( isset($punch_data[$employee][$i]) ){
> ...
> }else{
> echo "Absent\n";
> }
> /*...*/
> }
>
>
> I'm missing several steps but I hope you see the idea.
>
>

No, it's not crazy, and it's quite commonly done - I've seen it in a
bunch of different RDBMS's.

Doing it like this and everything can be handled in one SQL SELECT
statement. Not only will it be faster, but there's much less code to
maintain.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On May 8, 5:03*am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Álvaro G. Vicario wrote:
> > Mo escribió:
> >> 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 (to the MySQL DB ) which
> >> includes every date from here to ...., then just do an outer join.
>
> > That's crazy. You'd be overpopulating your DB with dumb data that will
> > need forever maintenance.
>
> > It's way easier than that:
>
> > 1. Fetch the available DB data into a PHP array
> > 2. In PHP, loop through the range of dates. For each date:
> > * * - If there is data, print it
> > * * - If not, print 'Absent'
>
> > SELECT UNIX_TIMESTAMP(DATE) AS DATE, USER, BLAH
> > FROM PUNCH_TIMES
> > WHERE .....
>
> > As about looping:
>
> > $from = mktime(0, 0, 0, 1, 1, 2008);
> > $to = time();
>
> > for($i=$from; $<=$to; $i=strtotime('+1 day', $i)){
> > * * echo date('d/m/Y', $i) . ":\n";
> > * * if( isset($punch_data[$employee][$i]) ){
> > * * * * ...
> > * * }else{
> > * * * * echo "Absent\n";
> > * * }
> > * * /*...*/
> > }
>
> > I'm missing several steps but I hope you see the idea.
>
> No, it's not crazy, and it's quite commonly done - I've seen it in a
> bunch of different RDBMS's.
>
> Doing it like this and everything can be handled in one SQL SELECT
> statement. *Not only will it be faster, but there's much less code to
> maintain.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Of course I always preffer speed of execution and simplicity of code,
but I've just come up with one concerne ...
Would we be able to generate a report (without errors) across a
NewYear date if we were numbering the days 1-366?

We have one group of our employees which gets paid on every 15th, so
even in our regular reports, we would encounter this at least once per
year.

~Mo

Mo wrote:
> On May 8, 5:03 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Álvaro G. Vicario wrote:
>>> Mo escribió:
>>>> 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 (to the MySQL DB ) which
>>>> includes every date from here to ...., then just do an outer join.
>>> That's crazy. You'd be overpopulating your DB with dumb data that will
>>> need forever maintenance.
>>> It's way easier than that:
>>> 1. Fetch the available DB data into a PHP array
>>> 2. In PHP, loop through the range of dates. For each date:
>>> - If there is data, print it
>>> - If not, print 'Absent'
>>> SELECT UNIX_TIMESTAMP(DATE) AS DATE, USER, BLAH
>>> FROM PUNCH_TIMES
>>> WHERE .....
>>> As about looping:
>>> $from = mktime(0, 0, 0, 1, 1, 2008);
>>> $to = time();
>>> for($i=$from; $<=$to; $i=strtotime('+1 day', $i)){
>>> echo date('d/m/Y', $i) . ":\n";
>>> if( isset($punch_data[$employee][$i]) ){
>>> ...
>>> }else{
>>> echo "Absent\n";
>>> }
>>> /*...*/
>>> }
>>> I'm missing several steps but I hope you see the idea.
>> No, it's not crazy, and it's quite commonly done - I've seen it in a
>> bunch of different RDBMS's.
>>
>> Doing it like this and everything can be handled in one SQL SELECT
>> statement. Not only will it be faster, but there's much less code to
>> maintain.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================- Hide quoted text -
>>
>> - Show quoted text -
>
> Of course I always preffer speed of execution and simplicity of code,
> but I've just come up with one concerne ...
> Would we be able to generate a report (without errors) across a
> NewYear date if we were numbering the days 1-366?
>
> We have one group of our employees which gets paid on every 15th, so
> even in our regular reports, we would encounter this at least once per
> year.
>
> ~Mo
>

No, you just need to check to see if this is a leap year or not.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On May 9, 10:03*am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Mo wrote:
> > On May 8, 5:03 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> Álvaro G. Vicario wrote:
> >>> Mo escribió:
> >>>> 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 (to the MySQL DB ) which
> >>>> includes every date from here to ...., then just do an outer join.
> >>> That's crazy. You'd be overpopulating your DB with dumb data that will
> >>> need forever maintenance.
> >>> It's way easier than that:
> >>> 1. Fetch the available DB data into a PHP array
> >>> 2. In PHP, loop through the range of dates. For each date:
> >>> * * - If there is data, print it
> >>> * * - If not, print 'Absent'
> >>> SELECT UNIX_TIMESTAMP(DATE) AS DATE, USER, BLAH
> >>> FROM PUNCH_TIMES
> >>> WHERE .....
> >>> As about looping:
> >>> $from = mktime(0, 0, 0, 1, 1, 2008);
> >>> $to = time();
> >>> for($i=$from; $<=$to; $i=strtotime('+1 day', $i)){
> >>> * * echo date('d/m/Y', $i) . ":\n";
> >>> * * if( isset($punch_data[$employee][$i]) ){
> >>> * * * * ...
> >>> * * }else{
> >>> * * * * echo "Absent\n";
> >>> * * }
> >>> * * /*...*/
> >>> }
> >>> I'm missing several steps but I hope you see the idea.
> >> No, it's not crazy, and it's quite commonly done - I've seen it in a
> >> bunch of different RDBMS's.
>
> >> Doing it like this and everything can be handled in one SQL SELECT
> >> statement. *Not only will it be faster, but there's much less code to
> >> maintain.
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================- Hide quoted text -
>
> >> - Show quoted text -
>
> > Of course I always preffer speed of execution and simplicity of code,
> > but I've just come up with one concerne ...
> > Would we be able to generate a report (without errors) across a
> > NewYear date if we were numbering the days 1-366?
>
> > We have one group of our employees which gets paid on every 15th, so
> > even in our regular reports, we would encounter this at least once per
> > year.
>
> > ~Mo
>
> No, you just need to check to see if this is a leap year or not.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

So, if I generate a report this year (2008) for the 12/16/07 - 1/15/08
date range, how do I make it identify the day numbers from December as
being from a prior year?
If I'm understanding all this correctly, our range of day numbers
would be 349-15.
Would I just have to use some function to extract the year value from
the date range provided by the user via the form?

~Mo

Mo wrote:
> On May 9, 10:03 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Mo wrote:
>>> On May 8, 5:03 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> Álvaro G. Vicario wrote:
>>>>> Mo escribió:
>>>>>> 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 (to the MySQL DB ) which
>>>>>> includes every date from here to ...., then just do an outer join.
>>>>> That's crazy. You'd be overpopulating your DB with dumb data that will
>>>>> need forever maintenance.
>>>>> It's way easier than that:
>>>>> 1. Fetch the available DB data into a PHP array
>>>>> 2. In PHP, loop through the range of dates. For each date:
>>>>> - If there is data, print it
>>>>> - If not, print 'Absent'
>>>>> SELECT UNIX_TIMESTAMP(DATE) AS DATE, USER, BLAH
>>>>> FROM PUNCH_TIMES
>>>>> WHERE .....
>>>>> As about looping:
>>>>> $from = mktime(0, 0, 0, 1, 1, 2008);
>>>>> $to = time();
>>>>> for($i=$from; $<=$to; $i=strtotime('+1 day', $i)){
>>>>> echo date('d/m/Y', $i) . ":\n";
>>>>> if( isset($punch_data[$employee][$i]) ){
>>>>> ...
>>>>> }else{
>>>>> echo "Absent\n";
>>>>> }
>>>>> /*...*/
>>>>> }
>>>>> I'm missing several steps but I hope you see the idea.
>>>> No, it's not crazy, and it's quite commonly done - I've seen it in a
>>>> bunch of different RDBMS's.
>>>> Doing it like this and everything can be handled in one SQL SELECT
>>>> statement. Not only will it be faster, but there's much less code to
>>>> maintain.
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstuck...@attglobal.net
>>>> ==================- Hide quoted text -
>>>> - Show quoted text -
>>> Of course I always preffer speed of execution and simplicity of code,
>>> but I've just come up with one concerne ...
>>> Would we be able to generate a report (without errors) across a
>>> NewYear date if we were numbering the days 1-366?
>>> We have one group of our employees which gets paid on every 15th, so
>>> even in our regular reports, we would encounter this at least once per
>>> year.
>>> ~Mo
>> No, you just need to check to see if this is a leap year or not.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================- Hide quoted text -
>>
>> - Show quoted text -
>
> So, if I generate a report this year (2008) for the 12/16/07 - 1/15/08
> date range, how do I make it identify the day numbers from December as
> being from a prior year?
> If I'm understanding all this correctly, our range of day numbers
> would be 349-15.
> Would I just have to use some function to extract the year value from
> the date range provided by the user via the form?
>
> ~Mo
>

At this point you're getting farther out of the PHP range and more into
MySQL. You need to be asking this in comp.databases.mysql.

It can be done - but this isn't a PHP problem.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On May 9, 4:52*pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Mo wrote:
> > On May 9, 10:03 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> Mo wrote:
> >>> On May 8, 5:03 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >>>> Álvaro G. Vicario wrote:
> >>>>> Mo escribió:
> >>>>>> 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 (to the MySQL DB ) which
> >>>>>> includes every date from here to ...., then just do an outer join.
> >>>>> That's crazy. You'd be overpopulating your DB with dumb data that will
> >>>>> need forever maintenance.
> >>>>> It's way easier than that:
> >>>>> 1. Fetch the available DB data into a PHP array
> >>>>> 2. In PHP, loop through the range of dates. For each date:
> >>>>> * * - If there is data, print it
> >>>>> * * - If not, print 'Absent'
> >>>>> SELECT UNIX_TIMESTAMP(DATE) AS DATE, USER, BLAH
> >>>>> FROM PUNCH_TIMES
> >>>>> WHERE .....
> >>>>> As about looping:
> >>>>> $from = mktime(0, 0, 0, 1, 1, 2008);
> >>>>> $to = time();
> >>>>> for($i=$from; $<=$to; $i=strtotime('+1 day', $i)){
> >>>>> * * echo date('d/m/Y', $i) . ":\n";
> >>>>> * * if( isset($punch_data[$employee][$i]) ){
> >>>>> * * * * ...
> >>>>> * * }else{
> >>>>> * * * * echo "Absent\n";
> >>>>> * * }
> >>>>> * * /*...*/
> >>>>> }
> >>>>> I'm missing several steps but I hope you see the idea.
> >>>> No, it's not crazy, and it's quite commonly done - I've seen it in a
> >>>> bunch of different RDBMS's.
> >>>> Doing it like this and everything can be handled in one SQL SELECT
> >>>> statement. *Not only will it be faster, but there's much less code to
> >>>> maintain.
> >>>> --
> >>>> ==================
> >>>> Remove the "x" from my email address
> >>>> Jerry Stuckle
> >>>> JDS Computer Training Corp.
> >>>> jstuck...@attglobal.net
> >>>> ==================- Hide quoted text -
> >>>> - Show quoted text -
> >>> Of course I always preffer speed of execution and simplicity of code,
> >>> but I've just come up with one concerne ...
> >>> Would we be able to generate a report (without errors) across a
> >>> NewYear date if we were numbering the days 1-366?
> >>> We have one group of our employees which gets paid on every 15th, so
> >>> even in our regular reports, we would encounter this at least once per
> >>> year.
> >>> ~Mo
> >> No, you just need to check to see if this is a leap year or not.
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================- Hide quoted text -
>
> >> - Show quoted text -
>
> > So, if I generate a report this year (2008) for the 12/16/07 - 1/15/08
> > date range, how do I make it identify the day numbers from December as
> > being from a prior year?
> > If I'm understanding all this correctly, our range of day numbers
> > would be 349-15.
> > Would I just have to use some function to extract the year value from
> > the date range provided by the user via the form?
>
> > ~Mo
>
> At this point you're getting farther out of the PHP range and more into
> MySQL. *You need to be asking this in comp.databases.mysql.
>
> It can be done - but this isn't a PHP problem.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

cool, thanks