SELECTing dates PHP/MySQL

Hi,

I am trying to select a week according to the current $date. I want to select the week that falls between the start and finish dates. What SQL query do I use, I'm confused.

week start finish 1 2006-10-09 2005-10-15

 

You can do it all right in your select statement

SELECT *
FROM `test`
WHERE `when` >= DATE_SUB( now( ) , INTERVAL dayofweek( now( ) ) -1
DAY )
AND `when` <= date_add( now( ) , INTERVAL 7 - dayofweek( now( ) )
DAY ) 

Code (markup):

Assuming your table is called "test" and you have a field of type date called "when"

 

Shoot I'm confused.

The table is not like that.
The table has a start date and a finish date. And the current date has to be between those two dates.

Table `week`

week start finish
1 2006-10-09 2006-10-15
2 2006-10-16 2006-10-29

 

SELECT * FROM table WHERE current_date BETWEEN start_date AND finish_date

 

Thanks I think that should work.

 

Why would you have a table to store the dates of each week when they can be generated automatically?

 

Haha, certain weeks do certain things according to another MySQL table. A week can actually consist of more than 7 days in this case. I need to SELECT a the right `week` where the $currentdate is between the `startdate` and the `finishdate`