Novice looking for advice (date comparison).

#1

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.)

#2

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) com> wrote:

Quote:

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.

#3

On 8 May, 12:37, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

Quote:

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) 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.

#4

On 8 May, 14:04, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

Quote:

On 8 May, 12:37, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) 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 -

Touché

#5

On 8 May, 14:04, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

Quote:

On 8 May, 12:37, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) 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é

#6

On May 8, 5:04*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

Quote:

On 8 May, 12:37, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) 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 -

I'd really like to understand both options fully prior to making a
decision.
Can you further explain the suggestion of creating dates on the fly by
querying against a table with day numbers?

~Mo

#7

On 8 May, 19:47, Mo <Mehile.Orl... (AT) gmail (DOT) com> wrote:

Quote:

On May 8, 5:04 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

On 8 May, 12:37, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) 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 -

I'd really like to understand both options fully prior to making a
decision.
Can you further explain the suggestion of creating dates on the fly by
querying against a table with day numbers?

~Mo

OK, here is an example. Suppose you have a table called dates, which
contains 366 rows. The rows do not need to contain anything, just an
empty field will do.

Now, suppose you want to produce a list of all dates from tomorrow for
one year:
SET @d =0;
SELECT
MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + ( @d := @d +1 ) ) calendar
FROM `dates` ;

Alternatively, if dates contained an integer column with the numbers 1
to 366 (let's call the column daynum):
SELECT
MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + daynum ) calendar
FROM `dates` ;

#8

On May 9, 6:58*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

Quote:

On 8 May, 19:47, Mo <Mehile.Orl... (AT) gmail (DOT) com> wrote:

On May 8, 5:04 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

On 8 May, 12:37, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) 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 everydate
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 learnhow
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 createa
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 -

I'd really like to understand both options fully prior to making a
decision.
Can you further explain the suggestion of creating dates on the fly by
querying against a table with day numbers?

~Mo

OK, here is an example. Suppose you have a table called dates, which
contains 366 rows. The rows do not need to contain anything, just an
empty field will do.

Now, suppose you want to produce a list of all dates from tomorrow for
one year:
SET @d =0;
SELECT
*MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + ( @d := @d +1 ) ) *calendar
FROM `dates` ;

Alternatively, if dates contained an integer column with the numbers 1
to 366 (let's call the column daynum):
SELECT
*MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + daynum ) *calendar
FROM `dates` ;- Hide quoted text -

- Show quoted text -

Wow, that's pretty cool. Being a novice, I've never come across the
MAKEDATE function before.

So in theory, I can create the dates on the fly, load them into an
array and use them from there.
Is there a counter function to MAKEDATE where it would take a date,
and provide the day number from that?
This would allow me to generate the complete date list for the range
specified, whether it be 30 days or 752 days.

~Mo

#9

On May 9, 7:49*am, Mo <Mehile.Orl... (AT) gmail (DOT) com> wrote:

Quote:

On May 9, 6:58*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

On 8 May, 19:47, Mo <Mehile.Orl... (AT) gmail (DOT) com> wrote:

On May 8, 5:04 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

On 8 May, 12:37, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) 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 someinput
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 anddate
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 -

I'd really like to understand both options fully prior to making a
decision.
Can you further explain the suggestion of creating dates on the fly by
querying against a table with day numbers?

~Mo

OK, here is an example. Suppose you have a table called dates, which
contains 366 rows. The rows do not need to contain anything, just an
empty field will do.

Now, suppose you want to produce a list of all dates from tomorrow for
one year:
SET @d =0;
SELECT
*MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + ( @d := @d +1 ) ) *calendar
FROM `dates` ;

Alternatively, if dates contained an integer column with the numbers 1
to 366 (let's call the column daynum):
SELECT
*MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + daynum ) *calendar
FROM `dates` ;- Hide quoted text -

- Show quoted text -

Wow, that's pretty cool. Being a novice, I've never come across the
MAKEDATE function before.

So in theory, I can create the dates on the fly, load them into an
array and use them from there.
Is there a counter function to MAKEDATE where it would take a date,
and provide the day number from that?
This would allow me to generate the complete date list for the range
specified, whether it be 30 days or 752 days.

~Mo- Hide quoted text -

- Show quoted text -

I just kept digging throught the MySQL Ref Manual and found it :
DAYOFYEAR()
I'm still interested in any input or advise regarding this issue.

Thanks-a-bunch,
Mo

#10

On 9 May, 15:49, Mo <Mehile.Orl... (AT) gmail (DOT) com> wrote:

Quote:

On May 9, 6:58 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

On 8 May, 19:47, Mo <Mehile.Orl... (AT) gmail (DOT) com> wrote:

On May 8, 5:04 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

On 8 May, 12:37, strawberry <zac.ca... (AT) gmail (DOT) com> wrote:

On 7 May, 17:32, Mo <Mehile.Orl... (AT) gmail (DOT) 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 -

I'd really like to understand both options fully prior to making a
decision.
Can you further explain the suggestion of creating dates on the fly by
querying against a table with day numbers?

~Mo

OK, here is an example. Suppose you have a table called dates, which
contains 366 rows. The rows do not need to contain anything, just an
empty field will do.

Now, suppose you want to produce a list of all dates from tomorrow for
one year:
SET @d =0;
SELECT
MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + ( @d := @d +1 ) ) calendar
FROM `dates` ;

Alternatively, if dates contained an integer column with the numbers 1
to 366 (let's call the column daynum):
SELECT
MAKEDATE( 2008, DAYOFYEAR( NOW( ) ) + daynum ) calendar
FROM `dates` ;- Hide quoted text -

- Show quoted text -

Wow, that's pretty cool. Being a novice, I've never come across the
MAKEDATE function before.

So in theory, I can create the dates on the fly, load them into an
array and use them from there.
Is there a counter function to MAKEDATE where it would take a date,
and provide the day number from that?
This would allow me to generate the complete date list for the range
specified, whether it be 30 days or 752 days.

~Mo

Errr, I used that "counter function" in the sample query, to make it
count from today! See if you can spot it.

And by the way, even being "an expert", I had never come across the
MAKEDATE function before either. I just assumed that there would be
date/time functions that could help me out, so I went to the manual
page for date/time functions and looked down the list till I found a
likely candidate.

Believe it or not, you could haev dome the same.