homepage Welcome to WebmasterWorld Guest from 54.196.197.153
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
DISTINCT not working
Mr_Cat




msg:4651592
 8:36 pm on Mar 5, 2014 (gmt 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!?

 

DaveWave




msg:4651861
 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




msg:4651998
 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)

DaveWave




msg:4652029
 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




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

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

Mr_Cat




msg:4653243
 10:00 am on Mar 12, 2014 (gmt 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?

DaveWave




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

[thegeekstuff.com...]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved