Avoiding duplicates

[chomedey]

Hi,

 

I'm trying to set up a "Notify me when someone comments on a blog entry I have commented on" script, similar to what you get in facebook.

 

I have a comments table, where the username, comment, and blog entry number are entered whenever someone comments.  And a preferences table, which I have joined with the comments table.

 

The only problem is that when I pull all the usernames from the comments table of users who have commented on a particular blog entry, to send them a notification, a user who has commented ten times on a particular blog entry gets ten notifications.  You see the problem?

 

Is there any way of telling mysql (or by using php) to only send one notification per username? (LIMIT 1 won't work, obviously, as there may be more than one user who has commented on a particular blog entry.  I need something like a LIMIT DISTINCT usernames).

 

Any help much appreciated.

 

Julian

[Mchl]

SELECT DISTINCT

[chomedey]

I tried that but it doesn't solve the problem - because each row is distinct, albeit with the same username in several.

 

When you use SELECT DISTINCT what column does it look to for distinctness?  Can you set which column it looks to?

 

Thanks.

 

Julian

[DavidAM]

SELECT DISTINCT looks for distinct rows.  You need to select only the fields that you need AND are distinct:

 

SELECT DISTINCT userid, blogid FROM ...

 

or if you need other data, say how many comments the user has made on the blog, you can use GROUP BY:

 

SELECT userid, blogid, COUNT(commentid) FROM ... GROUP BY userid, blogid

 

which is distinct by virtue of the GROUP BY phrase

 

 

[chomedey]

Great!  That works.

 

Thanks so much.

 

Julian

[chomedey]

One more quick question ...

 

If I am commenting on my own blog post (in response to someone else's comment, say) I obviously don't need to be notified about it.  Is it possible to exclude those rows in which the username is my own?

 

Thanks.

 

Julian

[chomedey]

Sorry - stupid question.  I did it with an addition to the WHERE clause.

 

Thanks again.

 

Julian