PHP/MySQL消息传递系统

Question

嗨，我正在尝试用php和mysql制作一个消息传递系统。

mysql表很简单: id发送方接收者文本时间戳

我试图使消息有点像Facebook/Twitter，因此列表在“对话”中，并且查看了中的最后一条消息。

这就是我的自动取款机：

(SELECT * FROM messages WHERE receiver = 13 OR sender = 13 GROUP BY receiver,sender ORDER BY id ASC) ORDER BY id ASC

Answer 1

SELECT messages.* FROM messages, (SELECT MAX(id) as lastid FROM messages 
WHERE receiver = 13 OR sender = 13 
GROUP BY CONCAT(LEAST(receiver,sender),'.',GREATEST(receiver,sender))) as conversations
WHERE id = conversations.lastid
ORDER BY timestamp DESC

你需要的是聊天伙伴之间的独特对话。我已经用子查询模拟了这一点，希望这有帮助

Answer 2

使用DESC获取新行，默认为ASC

   (SELECT * FROM messages WHERE receiver = 13 OR sender = 13 GROUP BY receiver,sender ORDER BY id DESC)

并在命令后设置限制 1 ,1

我觉得你需要

(receiver = '{receiver id}' AND sender = '{sender id}' ) OR (receiver ='{sender id}' AND sender = '{receiver id}' )

Answer 3

UPDAte：我不确定它是否工作得很完美：

SELECT * FROM messages 
WHERE receiver = 13
GROUP BY receiver,sender 
ORDER BY timestamp DESC
LIMIT 1

UNION ALL
SELECT * FROM messages 
WHERE sender = 13
GROUP BY receiver,sender 
ORDER BY timestamp DESC
LIMIT 1

为了扭转这一秩序：

ORDER BY timestamp DESC