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,

[tayloas]

In MySQL they have a command call WEEK. It returns the week of the year of the supplied date. You can use it on a wide variety of date formats.
ex.

SELECT WEEK('04-02-02');
5

Hope this helps.

-Brandon