Welcome to WebmasterWorld Guest from 54.145.58.37

Forum Moderators: open

Message Too Old, No Replies

DISTINCT not working

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

Junior Member

5+ Year Member

joined:Sept 15, 2008
posts: 90
votes: 0


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)

New User

joined:Feb 9, 2014
posts: 16
votes: 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)

Junior Member

5+ Year Member

joined:Sept 15, 2008
posts: 90
votes: 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)
4:35 pm on Mar 7, 2014 (gmt 0)

New User

joined:Feb 9, 2014
posts: 16
votes: 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)

New User

joined:Feb 9, 2014
posts: 16
votes: 0


Use Mysqldump to create scripts
[dev.mysql.com...]
10:00 am on Mar 12, 2014 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 15, 2008
posts: 90
votes: 0


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)

New User

joined:Feb 9, 2014
posts: 16
votes: 0

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members