Msg#: 4651590 posted 8:36 pm on Mar 5, 2014 (gmt 0)
Here's my query:
$act_q = "SELECT DISTINCT act_user_ID, act_datetime, member_name FROM activity_log"; $act_q.= " JOIN members ON activity_log.act_user_ID = members.member_ID"; $act_q.= " WHERE act_datetime BETWEEN '$month' AND NOW() ORDER BY member_name";
It was working fine until I added the 'WHERE', upon which it seems to be ignoring the DISTINCT now and returning me lots of results wit the same user name. Why!?
Msg#: 4651590 posted 10:52 pm on Mar 6, 2014 (gmt 0)
I guess this is to show all users who have done 1 or more things in a certain period. Since you've included act_datetime in your select statement it's going to return an entry for every activity unless the datetime is the same!
Change your first row to $act_q = "SELECT DISTINCT act_user_ID, member_name FROM activity_log";
Ignoring the fact that your fieldnames start with the table name I would prefer to see the query as
Select member_ID, member_name from members Where member_ID in (Select act_user_ID from activity_log WHERE act_datetime BETWEEN '$month' AND NOW()) ORDER BY member_name
I find the latter more readable and may perform better depending on the database engine you are running on.
Msg#: 4651590 posted 3:37 pm on Mar 7, 2014 (gmt 0)
Great! Thanks. Yes it's for an admin 'user tracking' system to monitor who goes where and when. I did get it to work eventually by removing the act_datetime in the first line as you say, but I will try your preferred method! I'm not so hot on sql and haven't used select within select yet.
Would you say a nested select is better than a join in general for performance?
(I'm not sure what the 'database engine' is! - just that I'm on an Apache server I think, using phpMyAdmin)