John W. Holmes <holmes072000@charter.net> wrote:
> There has to be a better way than this, but this is what I came up with
> before dinner...
>
> $dayofyear = date("z");
> $daysinyear = (date("L"))?366:365;
>
> $numover = ($dayofyear + 14) - $daysinyear
>
> if($numover > 0)
> {
> $sql = "DAYOFYEAR(dob) < $numover AND ";
> $twoweeks = $daysinyear;
> }
> else
> { $twoweeks = $dayofyear + 14; }
>
> $sql .= "DAYOFYEAR(dob) BETWEEN $dayofyear AND $twoweeks";
>
> $result = mysql_query("SELECT * FROM yourtable WHERE $sql");
I think the DAYOFYEAR approach has a little problem with leap years
(when the current year is a not leap year and the "birthday year" was a
leap year or vice versa).
See:
mysql> select dayofyear('2002-10-15');
+-------------------------+
| dayofyear('2002-10-15') |
+-------------------------+
| 288 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select dayofyear('1976-10-15');
+-------------------------+
| dayofyear('1976-10-15') |
+-------------------------+
| 289 |
+-------------------------+
1 row in set (0.00 sec)
And another problem:
# php -r 'echo date("z"),"\n";'
287
So I'd replace the year of the birthdays with the current year
and then see which birthdays fit:
select * from yourtable
where
concat(year(curdate()),'-',month(dob),'-',dayofmonth(dob))
between curdate() and adddate(curdate(),interval 14 day);
But obviously, this has a little problem around New Year's Eve. E.g. if
somebody's birthday is on January, 3rd, the SQL expression above will
only see that on January, 1st. On December 31st, it will use the current
year for the birthday's year, and then the between-expression won't
match. So just use another expression with year(curdate())+1:
select * from yourtable
where
concat(year(curdate()),'-',month(dob),'-',dayofmonth(dob))
between curdate() and adddate(curdate(),interval 14 day)
or
concat(year(curdate())+1,'-',month(dob),'-',dayofmonth(dob))
between curdate() and adddate(curdate(),interval 14 day);
Maybe there's still something missing, but I'd try that.
Regards...
Michael
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
