我正在尝试为私人消息创建一个用户收件箱,其中显示两个人之间的一条最新消息,以及按最近一次对所有对话进行排序。它应该像Facebook消息收件箱一样执行,您可以在其中单击最新消息的预览以查看完整的对话,并且对话也按最近的顺序排序。这是我到目前为止的声明,但它没有显示任何内容,我不知道如何解决它。有人有什么建议吗?
$sqlInbox = "
SELECT sender, receiver, message, parent, rView
FROM (SELECT sender, receiver, message, parent, rView
FROM messages
WHERE receiver='$log_username' OR sender='$log_username'
ORDER BY timestamp DESC)
GROUP BY parent
ORDER BY timestamp DESC";
父列表示完整的对话,因此两个设置的人员之间的任何消息,无论谁发送或接收,都将在表中具有相同的父 ID。提前谢谢。
我注意到的一件事是您需要按您选择的所有列进行分组。
SELECT sender, receiver, message, parent, rView
FROM (SELECT sender, receiver, message, parent, rView
FROM messages
WHERE receiver='$log_username' OR sender='$log_username'
ORDER BY timestamp DESC)
GROUP BY sender, receiver, message, parent, rView
ORDER BY timestamp DESC
检查这是否有效。
SELECT sender, receiver, message, parent, rView
FROM messages
WHERE receiver='$log_username' OR sender='$log_username'
ORDER BY parent ASC, timestamp DESC
睡在上面之后,逻辑就来了。以下内容似乎已经解决了我所有的问题。
$sqlInbox = "
SELECT a.*
FROM (SELECT * FROM messages
ORDER BY timestamp DESC) AS a
WHERE receiver='$log_username' OR sender='$log_username'
GROUP BY parent
ORDER BY timestamp DESC";