[SOLVED] Working with dates / queries / calendar

[iPixel]

I've tried to google what i could and im not quite sure how to phrase my search in order to get the results i want.

 

Here's what i'm interested in finding out.

 

I'll have a database with probably a basic 2 field table 

field1[ date ]

field2[ event info ]

 

How would i phrase my query in asking the following

 

SELECT * FROM events WHERE date is greater then today's date LIMIT 0,7

 

I mean will this work?

WHERE date > '09/11/2009' 

in whatever format the date is stored. ?

 

What im aiming to do is to only show  apreview of 1 weeks worth of events and then have  alink to open up a calendar that will show the entire moths worth.  But that's another story. Right now im not sure how to pull the correct rows of data.

 

Thanks Guys!

[cbolson]

Hi,

To start, I presume that your "date" field is in a datetime (or similar) format, otherwise this is going to be hard.

 

This will get results after today (based on server time)

SELECT * FROM events WHERE date >CURDATE()

This should get events after today and up until 7 days times:

SELECT * FROM events WHERE date >CURDATE() AND date<DATE_ADD(INTERVAL,CURDATE(),7 DAY)

Alternatively (better ? ):

SELECT * FROM events WHERE date BETWEEN DATE_ADD(CURDATE(),INTERVAL, 1DAY) AND DATE_ADD(INTERVAL,CURDATE,7 DAY)

You might have to play around with the numbers.... but this might help you in your way

 

Chris

 

[iPixel]

This is fantastic... exactly what i was looking for.

And yes, the field is DATE format .

 

Thank You!

[cbolson]

Good, I hope it sends you on your way.

 

Just realised that the "7 DAY" should probably be 8 DAY as you want days *after* today so tomorrrow plus 7 = 8

 

Chris