0

I have a page that has to display how many documents the user has given in for him to recieve commission

I tried count but it gives me totals, Im new to sql "count" and "group"

tabel has three fields:

id, user, date, refnum
1 - mike - 2012/01/01 - 12353
2 - Samy - 2012/01/01 - 12342
3 - mike - 2012/01/01 - 12325
4 - Jake - 2012/01/01 - 15315
5 - mike - 2012/01/02 - 15133
6 - mike - 2012/01/02 - 15512
6 - mike - 2012/01/02 - 12433

I want to show it like this

echo "".$user." has completed ".$records. " on ".$date;

my selet does not work, this is a mess.

$get_user_record_count = "SELECT user, COUNT(DISTINCT date) as 'date' FROM usertransactions GROUP BY user Where user='mike';"
    $extracted = mysql_query($get_user_record_count) or die(mysql_error());

    while ($rows = mysql_fetch_assoc($extracted))
    {

    $date = $rows['date'];
    $user = $rows['user'];
    //$records = $rows['count'];

    echo "".$user." has completed ".$records. " on ".$date;

    }

what i try and do is count the number of records per user per day.
and display in a list earliest date at the top

Mike has completed 2 documents on 2012/01/01
Mike has completed 3 documents on 2012/01/02

Hoe the peanut does one do this?

Edited by branding4you

2
Contributors
3
Replies
5
Views
4 Years
Discussion Span
Last Post by pritaeas
0

Group by user and date:

SELECT user, date, COUNT(*) AS count 
FROM usertransactions 
GROUP BY user, date 
WHERE user = 'mike'

Edited by pritaeas

0

Thank you, you pointed me in the right direction, yours gave syntax error on the Where so I moved the Where and it looks like this now

<?php
include_once "db_conf.php";

//GET DETAILS NEEDED FOR PROCESSING DATA
$sql = "SELECT user, date, COUNT(*) AS count FROM usertransactions WHERE user = 'mike' GROUP BY user, date ";

//CODE HERE TO DISPLAY
$extract = mysql_query($sql) or die(mysql_error());

while ($rows = mysql_fetch_assoc($extract))
    {

    $date = $rows['date'];
    $user = $rows['user'];
    $records = $rows['count'];

    echo "".$user." has completed ".$records. " on ".$date;
    echo "<br>";

    }


?>

Nice and neat like it should be. Thank you pritaeas! once again youre the master :D

Pritaeas for president!
Pritaeas for president!

1

Right, switched where and group by I see. Sorry to disappoint you, but the US does not allow foreigners to become president yet ;)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.