Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

DISTINCT not working



8:36 pm on Mar 5, 2014 (gmt 0)

5+ Year Member

Hi folks,

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!?


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.


3:37 pm on Mar 7, 2014 (gmt 0)

5+ Year Member

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)


4:35 pm on Mar 7, 2014 (gmt 0)

In this instance probably not, but it's more readable, particularly if you added a few more tables.


4:49 pm on Mar 9, 2014 (gmt 0)

Use Mysqldump to create scripts


10:00 am on Mar 12, 2014 (gmt 0)

5+ Year Member

Cheers, I had a look at mysql dump but am unclear as to how it can help generate scripts, it seems to just be a backup thing. What am I missing?


11:59 am on Mar 12, 2014 (gmt 0)

Featured Threads

Hot Threads This Week

Hot Threads This Month