Welcome to WebmasterWorld Guest from 54.226.194.180

Forum Moderators: open

Message Too Old, No Replies

DISTINCT not working

     

Mr_Cat

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

DaveWave

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.

Mr_Cat

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)

DaveWave

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.

DaveWave

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



Use Mysqldump to create scripts
[dev.mysql.com...]

Mr_Cat

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?

DaveWave

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

Featured Threads

Hot Threads This Week

Hot Threads This Month