MySQL & PHP: Send emails to the members who fall into last 3 active threads

Question

My query below will get the last 3 active threads from my blog's commnets,

SELECT ID, Approved, RecipientID, RecipientScreenname, RecipientEmail
FROM
(
SELECT 
root_strings.str_id as ID,
root_strings.str_approved as Approved,
root_strings.mem_id as RecipientID,

root_members_cfm.mem_screenname as RecipientScreenname,
root_members_cfm.mem_firstname as RecipientFirstname,
root_members_cfm.mem_email as RecipientEmail

FROM root_strings

LEFT JOIN root_members_cfm
ON root_members_cfm.mem_id = root_strings.mem_id

WHERE root_strings.parent_id = '1'
 AND root_strings.mem_id IS NOT NULL

UNION ALL

SELECT
root_strings.str_id as ID,
root_strings.str_approved as Approved,
root_strings.mem_id as RecipientID,

root_users.usr_screenname as RecipientScreenname,
root_users.usr_firstname as RecipientFirstname,
root_users.usr_email as RecipientEmail

FROM root_strings

LEFT JOIN root_users
ON root_users.usr_id = root_strings.usr_id

WHERE root_strings.parent_id = '1'
AND root_strings.usr_id IS NOT NULL

) SQ
ORDER BY ID DESC
LIMIT 0,3

It returns a result like this,

ID     Approved RecipientID  RecipientScreenname   RecipientEmail       
14      1           3          x                   x@yahoo.co.uk
13      n/a        NULL        y                   y@yahoo.co.uk
13      n/a        NULL        y                   y@yahoo.co.uk

Then I will an email to each of them.

foreach($items_thread as $item_thread) 
{
   $sentmail = mail($item_thread['RecipientEmail'],$email_subject,$email_content,$email_headers);
}

But the logic is not correct when you look closer as I will send y twice of the email!

y should just get one email. How can I fix it - should I fix the sql query or the php code?

score 0 · Answer 1 · answered Feb 17 '11 at 17:57

0

The problem is that NULL!=NULL (nor is NULL=NULL), so DISTINCT does not consider the 2nd and 3rd results to be the same. If you change your query so that RecipientID shows as 0 (e.g COALESCE(RecipientID,0) AS RID) the problem will go away.

answered Feb 17 '11 at 17:57

dnagirl

20,196
13
80
123

Or maybe `select distinct recipientemail` instead, since that should most likely be unique for each user. – Marc B Feb 17 '11 at 18:00

score 0 · Accepted Answer · edited May 23 '17 at 10:24

0

I have a solution for this now which is using array_unique, here it is Remove duplicate items from an array

edited May 23 '17 at 10:24

Community

1
1

answered Feb 19 '11 at 14:08

Run

54,938
169
450
748

MySQL & PHP: Send emails to the members who fall into last 3 active threads

2 Answers2

Linked