[SOLVED] Displaying events in the next 7 days

[Anzeo]

Hi all,

 

 

 

In my current project I have made a simple calendar (actually a list of events that are stored in a db) and now I'd like to add a smaller list to my sidebar which displays only the events that occur in the next 7 days.

 

So basicly, I want to write a query which returns only the events in the next 7 days (not including this day).

 

Can anyone help me out with this? Any help greatly appreciated!

 

Thanks in advance,

Anzeo

[akitchin]

you can use DATE_ADD() to figure out the dates one and seven days ahead from now, in combination with between:

 

SELECT stuff FROM table WHERE eventDate BETWEEN DATE_ADD(eventDate, INTERVAL 1 DAY) AND DATE_ADD(eventDate, INTERVAL 7 DAY)

[nathanmaxsonadil]

SELECT * FROM `events` ORDER BY `time` DESC LIMIT 7

[lemmin]

It has to be greater than the current date, though.

 

"...WHERE Date > NOW() ORDER BY Date LIMIT 7"

[akitchin]

i would suggest using my query if you wish to grab ALL events in the next 7 days (not including today), rather than just the 7 events that are the farthest in the future...

[Anzeo]

i would suggest using my query if you wish to grab ALL events in the next 7 days (not including today), rather than just the 7 events that are the farthest in the future...

 

Yup indeed. I'm going to give it a shot, stay tuned^^

[lemmin]

Wouldn't my query do exactly what he is asking? It would even include an event on the same day, but after the time (if it has a time). It wouldn't be the 7 events that are the farthest in the future, it would simply return the 7 that are closest to the current date starting after it. Am I wrong?

[Anzeo]

@ Lemmin: You're coorect, but I want all events of the next 7 days not just the next 7 events ^^

 

@Akitchin:

you can use DATE_ADD() to figure out the dates one and seven days ahead from now, in combination with between:

 

SELECT stuff FROM table WHERE eventDate BETWEEN DATE_ADD([color=red]eventDate[/color], INTERVAL 1 DAY) AND DATE_ADD([color=red]eventDate[/color], INTERVAL 7 DAY)

 

Shoudn't these be this day date in stead of the event date?

[akitchin]

@anzeo:  you're right, it should be NOW() rather than eventDate in those DATE_ADD() statements.

 

@lemmin:  you're right, i thought you were ordering by the Date in descending order.  my mistake.

[lemmin]

Oh, I see, sorry about that.

[Anzeo]

It's working! Thanks alot for the help and the time guys!