Showing the current week in a table

[locksheathsaint]

Hello,

Hopefully someone will be able to shed some light on this as I am stumped!

Basically I have a website were users enter deals into. I have two tables in my database:

One called "users" - This stores user information for example user ID username password etc.
Another table called "deals" - This stores deal information. Deal name, deal ID, user ID (which user did the deal), Deal amount, and Date the format for date is (YYYY-MM-DD).

I am now trying to display the total amount of deals done by each user for a current week for each day. For example in a 7 day calander type of style:

USERS SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY

USER 1 £65 £70 £56 £38 £87 £33 £44

USER 2 £65 £70 £56 £38 £87 £33 £44

USER 3 £65 £70 £56 £38 £87 £33 £44

Hopefully this makes sense so far!

These are the sql queries i've come up with so far...

This selects each deal done in the current week:

SELECT u.firstname, d.customername, d.dealamount, d.dealdate
FROM deals d, users u
WHERE u.uid = d.useriD
AND YEARWEEK( dealdate ) = YEARWEEK( CURRENT_DATE );

For example:

USER 1 DEAL1 £65 2013-03-15
USER 1 DEAL2 £65 2013-03-16


This puts down the total sum for the week by user:

SELECT SUM( d.dealamount ) , u.firstname
FROM deals d, users u
WHERE u.uid = d.userid
AND YEARWEEK( dealdate ) = YEARWEEK( CURRENT_DATE )
GROUP BY u.firstname

For example:

USER 1 £130

Can anyone help expand on this so it comes out with current days along the top?

Thanks in advanced!

[eval(BadCode)]

If you're wanting to do dynamically named columns (which is OK, and I typically do this when someone wants a "report" they could use in excel or whatever), you'll need to use at least two queries if you want to partition across a preset segment of time (like, tomorrow... your column headings would be different)... ie: your column headings are determined at runtime

you could alternatively do what you suggested, which is something like this:

| uid | total |SUNDAY | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY |

and then write it like this:

SELECT 
  u.uid as userID,
  SUM(dealamount) as total,
  SUM(IF(DAY(dealdate) = 'MONDAY', d.dealamount, 0)) as 'MONDAY',
  SUM(IF(DAY(dealdate) = 'TUESDAY', d.dealamount, 0)) as 'TUESDAY',
  SUM(IF(DAY(dealdate) = 'WEDNESDAY', d.dealamount, 0)) as 'WEDNESDAY',
  SUM(IF(DAY(dealdate) = 'THURSDAY', d.dealamount, 0)) as 'THURSDAY',
  SUM(IF(DAY(dealdate) = 'FRIDAY', d.dealamount, 0)) as 'FRIDAY',
  SUM(IF(DAY(dealdate) = 'SATURDAY', d.dealamount, 0)) as 'SATURDAY',
  SUM(IF(DAY(dealdate) = 'SUNDAY', d.dealamount, 0)) as 'SUNDAY',

FROM deals d 
JOIN users u 
  ON u.uid = d.useriD

WHERE YEARWEEK( dealdate ) = YEARWEEK( CURRENT_DATE )
GROUP BY u.uid; //This part is important because it is a summary/aggregate

edit:
just a note about style of code, please refrain from doing this:

FROM deals d, users u
WHERE u.uid = d.userid

it might not matter at first, but when you start writing more advanced queries this will cause MySQL to create the product of both tables (INSANELY INEFFICIENT)... so if you have 1,000,000 users and 1,000,000 deals.... MySQL may try to create a tmp table with 1,000,000,000,000 records to handle this kind of query.... I say try because it will basically just waste hours and hours of computing time and not work.
You wrote a cross join (or more notoriously called "a common join") and while MySQL may optimize this simple query, since it's such a common `mistake`, you should really be writing it like this:

SELECT ... FROM deals INNER JOIN users ON (deads.userid = users.uid) ...

Just to recap:
FROM a JOIN b ON a.id=b.aid
is short hand for
FROM a INNER JOIN b ON a.id=b.aid

and

FROM a,b
is short hand for
FROM a CROSS JOIN b
which is the cartesian product

That all being said, sometimes a comma/cross join is a very elegant way to solve a problem. It can be the perfect tool in certain circumstances.