MySQL 和 MySQL PHP：向属于最后 3 个活动线程的成员发送电子邮件

Question

我下面的查询将从我的博客的通讯网络中获取最后 3 个活动线程，

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

它返回这样的结果，

ID     Approved RecipientID  RecipientScreenname   RecipientEmail       
14      1           3          x                   [email protected]
13      n/a        NULL        y                   [email protected]
13      n/a        NULL        y                   [email protected]

然后我将向每个线程发送一封电子邮件。

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

但当你仔细观察时，逻辑并不正确，因为我将发送 y 两次电子邮件！

y 应该只会收到一封电子邮件。我该如何修复它 - 我应该修复 sql 查询还是 php 代码？

Answer 1

问题在于 NULL!=NULL （NULL=NULL 也不是），因此 DISTINCT 不认为第二个和第三个结果是相同的。如果您更改查询以使 RecipientID 显示为 0（例如 COALESCE(RecipientID,0) AS RID），问题就会消失。

Answer 2

我现在有一个解决方案，它使用 array_unique，这里是 删除重复的项目来自数组