Display current week as a 7 day calendar

[scooby1]

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

 

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

 

USER 1 £130

 

 

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

 

 

Thanks in advanced!

[Barand]

Set up empty arrays whose keys are the dates. Accumulate users totals into the arrays

 

 

$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

$d1 = new DateTime();
$d1->setTimestamp(strtotime('last Sunday'));
$d2 = clone $d1;
$d2->modify('+6 days');

$inc = new DateInterval('P1D');
$per = new DatePeriod($d1, $inc, 6);
foreach ($per as $d) {
    $blank_array[$d->format('Y-m-d')] = 0;
    $headings[] = $d->format('D jS');
}

$output = "<tr><td>User</td><td>" . join('</td><td>', $headings) . "</td></tr>\n";

$start = $d1->format('Y-m-d');
$end = $d2->format('Y-m-d');
$sql = "SELECT username, dealdate, SUM(dealamount) as tot
        FROM deals 
            JOIN user USING (user_id)
        WHERE dealdate BETWEEN '$start' AND '$end'
        GROUP BY username, dealdate";

$res = $db->query($sql) or die ($db->error);
#echo query2table($db,$sql);

$currUser = '';
while (list($user, $dt, $tot) = $res->fetch_row()) {
    if ($user != $currUser) {
        if ($currUser) {
            $output .= "<tr><td>$currUser</td><td>" . join('</td><td>', $totals) . "</td></tr>\n";
        }
        $currUser = $user;
        $totals = $blank_array;  // empty array with dates as keys
    }
    $totals[$dt] = $tot;
}       
$output .= "<tr><td>$currUser</td><td>" . join('</td><td>', $totals) . "</td></tr>\n";
?>

<table cellpadding="10">
    <?php echo $output?>
</table>       

[scooby1]

Fantastic mate, thank you for your help. I will give this a go tomorrow, i will update you and let you know how I get on. Thanks again!

[scooby1]

It works like a charm! This is brillaint! Is it possble to add in the number of deals for each day aswell using COUNT( d.dealamount ) AS nodeals. So it would then appear

Users  No.Deals sunday No.Deals monday
 
Tom       3        78       2     98
 

 

Thank you for help.

[Barand]

Yes it's possible, just use the same technique

[scooby1]

Cool yeah i'm understanding it now! One last thing here is my select statement:

 

SELECT u.username, d.dealdate, SUM(d.dealamount) as tot
        FROM deals d, users u
        WHERE u.uid = d.userid
        AND d.dealdate BETWEEN '$start' AND '$end'
        GROUP BY u.username, d.dealdate

It only shows users with deals so it doesnt show all the users even if they dont have a deal. Am I doing something wrong in the select statement? I've tried a join but it doesn't seem to work. Thanks again.

Edited March 17, 2013 by scooby1

[Barand]

Use explicit joins

FROM A JOIN B ON whatever

instead of

FROM A, B WHERE whatever.

It makes the structure of the query clearer and separates out the search criteria in the WHERE clause. Also, there are join types that have to use the explicit syntax.

If you want all users then you need to use a left join.

$sql = "SELECT username, dealdate, SUM(dealamount) as tot, COUNT(dealamount) as num
        FROM user u
            LEFT JOIN deals d
                ON u.user_id = d.user_id
                AND dealdate BETWEEN '$start' AND '$end'
        GROUP BY username, dealdate";

[scooby1]

Hello Barand,

 

That again worked perfectly, I have a field in my users table called admin which i put either 0 or 1 in if they are an administrator or a normal user. If the user is an administrator I don't want them to appear on the sales board. The left Join displays them all but if i add with the join:

 

SELECT u.username, d.dealdate, SUM(d.dealamount) as tot, COUNT(d.dealamount) as num
        FROM users u
		LEFT JOIN deals d ON u.uid = d.userid
                AND u.admin = 0
		AND d.dealdate BETWEEN '$start' AND '$end'
        GROUP BY u.username, d.dealdate

 

It still displays all the users aswell as administrators.

 

If I take the join out and just have:

 

SELECT u.username, d.dealdate, SUM(d.dealamount) as tot, COUNT(d.dealamount) as num
        FROM users u, deals d
	WHERE u.uid = d.userid
        AND u.admin = 0
	AND d.dealdate BETWEEN '$start' AND '$end'
        GROUP BY u.username, d.dealdate

It only displays users with added deals?

 

Any ideas? sorry to keep pestering you!!

 

 

[Barand]

put admin=0 in a WHERE clause

 

SELECT u.username, d.dealdate, SUM(d.dealamount) as tot, COUNT(d.dealamount) as num
        FROM users u
		LEFT JOIN deals d ON u.uid = d.userid
		AND d.dealdate BETWEEN '$start' AND '$end'
        WHERE u.admin = 0
        GROUP BY u.username, d.dealdate

[scooby1]

Ah magic, it's working. Thanks for all your help. Credit to this forum.