$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!?
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.
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)