Mysql + php : select count group by day and user

Question

i have the tables below

       USER                      COMMENT
---------------------     -----------------------
 |   id   |   name         | id | user_id | date
---------------------     -----------------------
 |   1    |     joe        | 1  |    1    | 2014-10-10
 |   2    |     jane       | 1  |    1    | 2014-10-10
 |   3    |     ted        | 1  |    3    | 2014-10-11

My aim is to create a stats comparaison chart. So i want to extract for each current week days, the number of comment added by each user. The expected array

-----------------------------
            2014-10-10
-----------------------------
joe  |  2
jane |  0
ted  |  0
------------------------------
            2014-10-11
------------------------------
joe  |  0
jane |  0
ted  |  1

I did a simple left join query and double group by, but the result was not formatted like expected.

Maybe, i have to sort and merge results using php?!

Thank you for the help

Your expected results don't match your data. Should Joe not have 2 comments on 10/10? — Siyual
– Siyual, Commented Jul 19, 2014 at 16:01
Formatting like this isn't MySQLs job. You will get that sorted with ORDER BY date DESC, COUNT(*) DESC, id ASC. While looping through your result, you've only watch the change in the date column. — VMai
– VMai, Commented Jul 19, 2014 at 16:12

Aman · Accepted Answer · 2014-07-19 19:54:12Z

1

What I would do is

Select User.name , Comment.date
From Comment Left Join User On Comment.user_id = User.id
Order By Comment.date Desc

This would return a list with user who commented + date.

Now I would parse the result set :-

$query = "query above";
$rowset = $db->fetchAll($query);

$result = array();
foreach ($rowset as $row) {
    $result[$row['date']][$row[User]] += 1
}

This would give you an array

Date1
    =>user1
      =>count
    =>user2
      =>count
Date2
    =>user3
      =>count
    =>user4
      =>count

answered Jul 19, 2014 at 19:54

Aman

6811 gold badge11 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

didierc Over a year ago

That's the php way: notice that you need to return the score for every users and every day! You should be able to retrieve the list of users with a first query.

didierc · Accepted Answer · 2014-07-21 14:56:53Z

1

Here's my attempt:

SELECT c1.date, c1.name, COUNT(c2.date)
FROM (
    SELECT DISTINCT c.date, u.name, u.id
    FROM COMMENT c
    CROSS JOIN USER u) c1
LEFT JOIN COMMENT c2
ON c1.id = c2.user_id AND c1.date = c2.date
GROUP BY c1.date, c1.id

The difficulty was to obtain a list of all users for each date, which I produced with the sub query c1.

edited Jul 21, 2014 at 14:56

answered Jul 19, 2014 at 17:23

didierc

14.8k3 gold badges35 silver badges52 bronze badges

5 Comments

Darren Over a year ago

Note: possibly a typo - u.I'd should be u.id? :-)

didierc Over a year ago

@Darren, Yes thanks! spelling autocorrect keeps bugging me whenever I write id; this one escaped me.

Darren Over a year ago

Perfectly alright, that's what we are all here for!

Strawberry Over a year ago

@Darren No higher purpose? :-(

Darren Over a year ago

@Strawberry In your case, it's being delicious!

Collectives™ on Stack Overflow

Mysql + php : select count group by day and user

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related