Filter dates that user entered our system

Navigation
0

Hello, how are you guys?

I'm trying to filter the dates that users connect to my system.

It has a but, first need to filter so it does not have duplicate users.

I'm already doing this with the DISTINCT command.

With the result that DISTINCT is giving me I want to search the DATA column, to know every day that these users have connected to my site.

I am using the following command:

select from wps_history where inserted between "2016-11-01 00:00:00" AND "2016-11-30 23:59:59"

This command brings me all the results, however this is giving more than 40 thousand lines, doing the filter DISTINCT the result and filtered for 500 lines, I am using the following command:

select DISTINCT user_id from wps_history where inserted between "2016-11-01 00:00:00" AND "2016-11-30 23:59:59"

Now I'm wondering how I can use the above command and bring the dates each USER_ID connected to my system.

I'll be waiting, thanks

    
asked by anonymous 09.12.2016 / 13:45

1 answer

0

Do not use DISTINCT for this, use GROUP BY for user code:

If you'd like the latest date: 

SELECT user_id,
       MAX(inserted)
  FROM wps_history
 WHERE inserted BETWEEN '2016-11-01 00:00:00' AND '2016-11-30 23:59:59'
 GROUP BY user_id

If you want the first date: 

SELECT user_id,
       MIN(inserted)
  FROM wps_history
 WHERE inserted BETWEEN '2016-11-01 00:00:00' AND '2016-11-30 23:59:59'
 GROUP BY user_id

If you want to group everything into one column: 

SELECT user_id,
       GROUP_CONCAT(DATE_FORMAT(inserted, '%d-%m-%Y') separator ', ') as datas
  FROM wps_history
 WHERE inserted BETWEEN '2016-11-01 00:00:00' AND '2016-11-30 23:59:59'
 GROUP BY user_id
    
09.12.2016 / 13:49
Update with generic CRUD post on facebook fanpage with php