At 5:03 PM -0700 9/4/01, Chad Berryman wrote:
>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.
You're making this way too hard, I suspect. To find all birthdays from
a target date to the target date plus a week, do this:
... WHERE birthday >= $target_date
AND birthday <= DATE_ADD($target_date,INTERVAL 7 DAY)
>
>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
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
