Selecting results based on current date and previous weeks

wedgeantilles

Hi,

I hope that someonee can help!

I need to display a number of different results for a number of different weeks, each preceding the current week. ie. if today is Tuesday 15th Feb, then i need the first recordset query to come back with results between Monday 7th Feb and Sunday 13th Feb, and then the next recordset query to run for the week preceding this.

So, it will look approx like this:

Current Week - 2005-02-07 - 2005-02-13
Week Minus 1 - 2005-02-06 - 2005-01-31
Week Minus 2 - 2005-01-30 - 2005-01-24
etc

I need help in writing a script that combines the folllowing parameters:

Determines the current date (ie. 2005-02-14)
Determines the current week period (ie. 2005-02-07 - 2005-02-13)
Determines the last 6 week period dates before the current date (ie 2005-02-06 - 2005-01-31, 2005-01-30 - 2005-01-24)
Stores these dates as variables to put into a SQL query.

It is important to note that the week period ALWAYS runs from Mon-Sun, therefore the script should not count the current date as the end of the current week. Otherwise, if you look at it on different days, you are going to get different information!

Any help is greatly appreciated, as it is fairly complex and I am not sure where to begin!

sfullman

dates are always complex. You can get the current week of the current day by doing

strtotime("-1 week");
strtotime("-7 day");

so says the manual

first get the first day of the week using date() function (adjust to get monday). just go to php.net/date and you'll have all the info you need. Then your number series will be:

-7,-14,-21 if today is monday
-8,-15,-22 if today is tuesday
etc.

This also gets you past the fact that going back 6 weeks may be into last year - this method will not be affected by that.

Sam

Roger_Ramjet

MySQL has date functions that are at least as comlete as those in PHP. So you don't have to mess around in code to get the values for a query, just use the appropriate native date function in the query.