Current month each day stats per user row

debugcn Published at Dev

kaidoj

I have 23 users in group 75(user_group).

Then I have table users_participated where row is set only if user participated ( sometimes it exists but is 0 ( not participate ) ).

Now I need to show each day for every user this month did he participate or not.

I can do it with php loop. But I was wondering if there is a way to do it only with single mysql query.

   //output html table should look like y = yes n = no 1,2,3,5 are days in month
   user | 1 | 2 | 3 | 4 | 5.. 
   user1| y | n | y | n | n
   user2| n | y | y | n | y

   // user table
   id | user   | group
   1  | user1  |  75
   2  | user2  |  75
   3  | user3  |  75
   4  | user4  |  68

   //participate table
   user_id | participated | date_added
      1    |  1           | 2014-03-29 11:03:00
      2    |  0           | 2014-03-29 11:03:00

//sometimes it can be 0 AND sometimes if its null its not even in this table if u know what i mean.

How can I do this ?

krokodilko

A simple crosstab query can do that:

SELECT u.user,
       max( if(day(p.date_added) = 1, p.participated, 0 ) ) day1,
       max( if(day(p.date_added) = 2, p.participated, 0 ) ) day2,
       ..............
       ..............
       ..............
       max( if(day(p.date_added) = 28, p.participated, 0 ) ) day28,
       max( if(day(p.date_added) = 29, p.participated, 0 ) ) day29,
       max( if(day(p.date_added) = 30, p.participated, 0 ) ) day30,
       max( if(day(p.date_added) = 31, p.participated, 0 ) ) day31
FROM user u
JOIN participate p
ON u.id = p.user_id
WHERE p.date_added >= '2014-03-01'
  AND p.date_added < '2014-04-01'
GROUP BY u.id

demo: http://sqlfiddle.com/#!9/cd31f/3

Collected from the Internet

Please contact [email protected] to delete if infringement.