Multiple row query

hi guys, ive asked for help on this issue in the PHP forums too but i dont seem to be getting anywhere so i wondered if someone could suggest how it could be accomplished in MYSQL.

I have a table with the following fields:
user_id, worked, auth and date.

what im trying to get is the amount of hours (from the ‘worked’ field) per person (based on ‘user_id’) per week (based on ‘date’).

[TABLE]
[TR]
[TD]user_id[/TD]
[TD]sum(worked)[/TD]
[TD]this week[/TD]
[TD]last week[/TD]
[TD]week before[/TD]
[TD]week before that[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]21[/TD]
[TD]3[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]789456[/TD]
[TD]17[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

Any ideas how my query would look to get such information?

thanks in advance.


SELECT
    userid
  , YEAR(datefield) AS theyear
  , WEEK(datefield) AS theweek
  , SUM(worked) AS worked
FROM tablename
GROUP BY userid, YEAR(datefield), WEEK(datefield)

To display the data in the form you posted, you’ll have to elaborate the query result set with PHP