gpt4 book ai didi

php - 对连接表有限制吗?

转载 作者:行者123 更新时间:2023-11-29 12:21:03 24 4
gpt4 key购买 nike

尝试构建一个允许多个收件人的线程消息传递模块。为此,我有一个收件人表,每行包含一个 message_id 和recipient_id。下面我创建了一个基本上是用户消息的列表。该代码可以工作,但由于创建连接的方式,如果有多个收件人,我会多次收到相同的消息,这使得无法限制唯一消息的数量。

是否可以添加消息数量限制,但仍然检索收件人列表,可能在语句中使用另一个选择?

我想声明下面的 php 准确地显示了我想要实现的目标。

$query = mysql_query(

"SELECT
messages.message_id,
messages.message_parent_id,
messages.message_from_id ,
messages.message_from_email,
messages.message_subject,
messages.message_body,
messages.message_sent_datetime,

u_from.forename AS from_forename,
u_from.surname AS from_surname,

m_recipients.recipient_user_id AS recipient_user_id,

u_to.forename AS to_forename,
u_to.surname AS to_surname

FROM messages

INNER JOIN users AS u_from ON messages.message_from_id = u_from.user_id
INNER JOIN message_recipients AS m_recipients ON messages.message_id = m_recipients.recipient_message_id
INNER JOIN users AS u_to ON m_recipients.recipient_user_id = u_to.user_id

WHERE m_recipients.recipient_user_id = 1 OR messages.message_from_id = 1

ORDER BY messages.message_sent_datetime DESC

");

$messages = [];

while($row=mysql_fetch_array($query)) {

if (array_key_exists($row['message_id'], $messages)) {
$messages[$row['message_id']]['to'][] = $row['to_forename'] . ' ' . $row['to_surname'];
}
else {
$messages[$row['message_id']] = [
'to' => [$row['to_forename'] . ' ' . $row['to_surname']],
'from' => $row['from_forename'] . ' ' . $row['from_surname'],
'subject' => $row['message_subject'],
'body' => $row['message_body'],
'datetime' => $row['message_sent_datetime']
];
}
}

最佳答案

下面将返回给您的消息,发件人和收件人姓名以逗号分隔。

这样就够了吗?

    SELECT
messages.message_id, messages.message_parent_id, messages.message_from_id ,
messages.message_from_email, messages.message_subject, messages.message_body,
messages.message_sent_datetime,

GROUP_CONCAT(CONCAT(u_from.forename, ' ', u_from.surname)) as from,
GROUP_CONCAT(CONCAT(u_to.forename, ' ', u_to.surname)) as to

FROM messages

INNER JOIN users AS u_from ON messages.message_from_id = u_from.user_id
INNER JOIN message_recipients AS m_recipients ON messages.message_id = m_recipients.recipient_message_id
INNER JOIN users AS u_to ON m_recipients.recipient_user_id = u_to.user_id

WHERE m_recipients.recipient_user_id = 1 OR messages.message_from_id = 1
GROUP BY messages.message_id
ORDER BY messages.message_sent_datetime DESC

您可能想使用 GROUP_CONCAT(DISTINCT )

关于php - 对连接表有限制吗?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28992434/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com