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?
