date manipulation ...

Anon

I'm setting up a dance vinyl latest releases database driven website using php and a mysql db,where the date released is stored in 04-02-02 format (probably) which would cover releases for the period 04-02-02 to 10-02-02, and 11-02-02 would cover 11-02-02 to 17-02-02 etc.

Now I want to display the results by week only (with a last 4 weeks option to)and I'm racking my brains (fairly limited) as to the best method (using php and/or sql) as to how do this.

My main problem is that if I get the current date using php or in an sql command how do I know which week it refers to, as in current_date = 10-02-02 would mean display all releases with a week_released = 04-02-02 in the date field.

Hope this makes sense.

Does anyone know the best way of doing this or even a better overall approach.

cheers,

Anon

I recall MySQL having some date functions, and I do know that PHP's date function will do what you want with a wee bit of work.

If you're storing this in a MySQL db, you'd be better of using a datatype suited for the task, rather than a string. Personally, I'd go for an integer type and store a Unix timestamp (not to be confused with a MySQL TIMESTAMP) generated by
list($day,$month,$year)=explode('-',$datestring);
$timestamp=mktime(0,0,0,$month,$day,$year+2000);

date('W',$timestamp) will give you the week of the year that the given date occurs in.
More useful is actually date('w',$timestamp), the day of the week (counting Sunday as 0).

$today=(the timestamp for the requested date);
$weekday=date('w',$today);
$firstdayofweek=$today-$weekday360024; //timestamp for Sunday
$lastdayofweek=$today+(7-$weekday)360024; //timestamp for next Sunday - i.e. up to the end of Saturday

If you're storing the Unix timestamps then these are the numbers you want to search between:

WHERE release>$firstdayofweek AND release<$lastdayofweek

otherwise you will of course need to convert the timestamps into terms that make sense compared to those in the db.

Getting a date in the same format as those supplied is simplicity itself:

$firstdate=date('d-m-y',$firstdayofweek);
$lastdate=date('d-m-y',$lastdayofweek);