I am trying to create a MySQL SELECT statement using PHP where I am getting
a filtered list back by a date in the database formatted as yyyy-mm-dd
where the date falls between a one week window that is in constant motion.
For example, take todays date (as a julian calendar number) and a date that
is set 7 days in the future (also as a julian calendar number and find all
rows in the database where the date field (as a julian number) falls
between the julian number of the window, i.e. today and one week from today.
The SELECT statement that I came up with is this
SELECT *,DATE_FORMAT(birthday,'%M %d'),
DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d'))
FROM table_name
WHERE DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d')) >= $yday AND
DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d')) <= $yday_hi";
The PHP variables $yr, $yday, and $yday_hi are set as
$yr is the current 4 digit year
$yday is the current julian calendar date for today
$yday_hi is the julian calendar date one week in the future.
This select statement works fine until I get to December 24th of any year,
and the $yday_hi goes beyond the actual number of julian days in this year.
So I have created an if statement that says if the date in the future is
bigger than the julian date for December 31 of this year, then subtract the
2 and set the date in the future as the difference.
For example, if there are 364 days this year, and today's julian calendar
date is 360, the one week in the future would be (360 + 7) or 367. So
subtract 364 from 367 which is 3 and set the $yday_hi variable to 3.
This makes the numbers work. So if today is December 30th (or 364) the week
in the future date would become Jan 6 (or 6 as a julian date).
It is at this time that the select statement look like
SELECT ....... WHERE birthday >= 364 (today) AND <= 6 (one week from today)
My select statement will ONLY work if $yday is smaller than $yday_hi.
Is there a way to make MySQL understand that I need dates at the end of the
year, and at the beginning ??
I hope that makes sense.
Chad
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
