Show the for the next 7 days.

[WizyWyg]

Hi all,

(Working with PHP and MySQL)

I have table "events" that list events by the day they happen.

How can I have it show only those events that are going to happen in the next 7 days from "now"?

Currently i have the following code:

SELECT * FROM events WHERE TO_DAYS(eventdate) - TO_DAYS(now()) <= 7 AND eventscat_id=1;

But it will show events that happened before now() as well.

I guess I need something in the where clause to not include anything that is <= 0 when the where clause subtracts now() from the eventdate.

Any ideas?

 

[sleipnir214]

Why not just use PHP to calculate the future cutoff date, then include that with a query like:

SELECT * FROM events WHERE eventdate >= now() and eventdate <= $future_date and eventscat_id = 1
Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[WizyWyg]

$future_date (?) dont have a variable/function for that and I wanted to work with what i have:

So, this works:

SELECT * FROM events WHERE eventdate >= now() AND eventdate <= now() + INTERVAL 7 DAY AND eventscat_id=3

 

[LittleHavoc]

This is the correct answer if the field type is date.
Careful with datetime and timestamp, as events that are sheduled for 5 mins will not be displayed.

Had this problem with my events system, I held 2 dates/times: start and end. They had to include date and time cos they could span several days. So had to create a constant in the PHP code &quot;THIS_MORNING&quot; and use that in the queries.

(P.S. use of timestamp is not advised unless for things such as last updated)