PHP-MySQL DateQuestion

[Hinrich Specht]

Hi,

I´d like to display anniversary greetings on my website. The annyversaries
are saved in a mysql-table in the mysql DATE-Format (YYYY-MM-DD). Now I need
to select all rows where the day and the month is between now and +7 days
(to show the anniversaries coming up within the next week). My question is:
Is there a way to select these rows directly from the DB without recognizing
the year? Or do I have to query all rows and parse the dates in code?
Any help appreciated!

Thanks in advance,

Hinrich

[Sugapablo]

Re: PHP-MySQL DateQuestion

<?php

$day = date("Y-m-d");
$day_seven = date("Y-m-d", mktime(0,0,0, date(m), date(d)+7,date( Y)));

$sql = "SELECT * FROM your_table WHERE date_column >= ".$day." AND <=
$day_seven";

?>


--
Sugapablo
------------------------------------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal

[Sugapablo]

Re: PHP-MySQL DateQuestion

Sugapablo wrote:
[color=blue]
> $sql = "SELECT * FROM your_table WHERE date_column >= ".$day." AND <=
> ".$day_seve n;[/color]

^ better.

--
Sugapablo
------------------------------------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal

[Hinrich Specht]

Re: PHP-MySQL DateQuestion

Thanks for your answer, but I´m afraid that won´t work...

in your example $day looks like "2003-09-25",
the entries in date_column look like "1974-12-15", ...
this means the value in date_column could never be greater than $day (except
in the case I can foresee a birth, but that happens quite seldom,
unfortunately).
That´s why I asked if there´s a way to build a select where the Year in the
date_column is NOT recognized...

Hinrich

"Sugapablo" <russREMOVE@sug apablo.com> schrieb im Newsbeitrag
news:vn5il0mj1c 9u9f@corp.super news.com...[color=blue]
> Sugapablo wrote:
>[color=green]
> > $sql = "SELECT * FROM your_table WHERE date_column >= ".$day." AND <=
> > ".$day_seve n;[/color]
>
> ^ better.
>
> --
> Sugapablo
> ------------------------------------
> http://www.sugapablo.com <--music
> http://www.sugapablo.net <--personal
>[/color]

[Pedro]

Re: PHP-MySQL DateQuestion

Hinrich Specht wrote:[color=blue]
> Hi,
>
> I´d like to display anniversary greetings on my website. The annyversaries
> are saved in a mysql-table in the mysql DATE-Format (YYYY-MM-DD). Now I need
> to select all rows where the day and the month is between now and +7 days
> (to show the anniversaries coming up within the next week). My question is:
> Is there a way to select these rows directly from the DB without recognizing
> the year? Or do I have to query all rows and parse the dates in code?
> Any help appreciated![/color]

slight tweaking of dates :)

<?php
$now0 = date('m')*100+d ate('d');
$now7 = date('m', time()+7*24*60* 60)*100+date('d ', time()+7*24*60* 60);
?>

and build the sql with $now0 and $now7

select x, y, z
from yourtable
where month(datefield )*100+day(datef ield) between $now0 and $now7


Happy Coding :)

--
I have a spam filter working.
To mail me include "urkxvq" (with or without the quotes)
in the subject line, or your mail will be ruthlessly discarded.

[Tom Thackrey]

Re: PHP-MySQL DateQuestion


On 25-Sep-2003, "Hinrich Specht" <hspecht74@gmx. de> wrote:
[color=blue]
> I´d like to display anniversary greetings on my website. The annyversaries
> are saved in a mysql-table in the mysql DATE-Format (YYYY-MM-DD). Now I
> need
> to select all rows where the day and the month is between now and +7 days
> (to show the anniversaries coming up within the next week). My question
> is:
> Is there a way to select these rows directly from the DB without
> recognizing
> the year? Or do I have to query all rows and parse the dates in code?
> Any help appreciated![/color]

The following will work for every thing but the anniversaries in first week
of the year:
select * from mytable where dayofyear(anniv date)>=dayofyea r(curdate()) and
dayofyear(anniv date)<=dayofyea r(curdate())+7

to cover it you need
select * from mytable where (dayofyear(anni vdate)>=dayofye ar(curdate()) and
dayofyear(anniv date)<=dayofyea r(curdate())+7) or
(dayofyear(anni vdate)+365>=day ofyear(curdate( )) and
dayofyear(anniv date)+365<=dayo fyear(curdate() )+7)

This still ignores the problem of leap years, but I'll leave that up to you.


--
Tom Thackrey

https://www.creative-light.com