Showing events based on date

[rjrasmussen]

I suppose this is easy for a lot of the people who are familiar with PHP/MySQL.
I'm relatively new to the whole thing...so please bear with me.

I have a single table entitled "events" with two fields. One field is the "date" field and the other is the "event" field. I have a whole bunch of dated events that run into next year.

I'd like to only show the events for two weeks. So as an example, if you were to go to my website today (today is 11.10.08), you'd see all of the events from 11.9.08 through 11.22.08.

Can anyone lend a hand?

Any/all help is GREATLY appreciated! Thank you.

[Rhyan]

You can check here http://bg.php.net/manual/en/function.time.php
There is an example how to calculate next week according to current time.

My idea is the following.
Store current date as $current_date and calculate future date and store it as $future_date.
Then run this query in the database :

$query = 'SELECT event, date FROM yourdb WHERE date between \''.$current_date.'\' and \''.$future_date.'\';
$run_query = mysql_query($query, $connection);
while($result=mysql_fetch_assoc($run_query)
 {
echo $result['event'].$result['date'];
 }
else
 {
echo 'No events within next two weeks';
 }

Note that my syntax may not be completely correct, so you should fix it according your requirements, but I hope you get the idea.

[Manuz]

This may be simple ......

SELECT * from TABLE WHERE Date between CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)

[Rhyan]

This may be simple ......

SELECT * from TABLE WHERE Date between CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)

Agree, date_add function in mysql will do, however, Manuz, do you have any idea if you can use it on varchar columns. I mean, I suppose mysql date_add will work for columns of type DATETIME, while it will not work on varchar columns.

If your table just contains strings as php passes it to the database, I think you should first convert the stored string to date using str_to_date() function in mysql, and then make the dateadd... etc..

[Manuz]

Agree, date_add function in mysql will do, however, Manuz, do you have any idea if you can use it on varchar columns. I mean, I suppose mysql date_add will work for columns of type DATETIME, while it will not work on varchar columns.

If your table just contains strings as php passes it to the database, I think you should first convert the stored string to date using str_to_date() function in mysql, and then make the dateadd... etc..

Yea Rhyan...i suggested for the field with date type only..........
otherwise they can use urz code..

[rjrasmussen]

This may be simple ......

SELECT * from TABLE WHERE Date between CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 14 DAY)

BINGO! I mean, I was on the right track with DATE_ADD, but I wasn't using BETWEEN.
Good stuff.

As always, I truly appreciate the help.
Thank you.