我有一个名为"message"的表,我想显示用户之间的所有对话(最后一条消息)
表格结构:
message_id | user_id | recipient_id | message | status | date
示例行:
1 | 1 | 2 | Hello | 0 | 2016-03-26 12:00:00
2 | 2 | 1 | Hi | 0 | 2016-03-26 12:05:00
3 | 1 | 3 | Are you there? I want meet you! :P | 0 | 2016-03-26 12:20:00
4 | 1 | 2 | How are you? | 0 | 2016-03-26 12:10:00
5 | 2 | 1 | Fine :) | 0 | 2016-03-26 12:15:00
6 | 5 | 1 | Hi :D | 0 | 2016-03-26 15:00:00
所以,结果应该是(对于user_id==1):
3 | 1 | 3 | Are you there? I want meet you! :P | 0 | 2016-03-26 12:20:00
5 | 2 | 1 | Fine :) | 0 | 2016-03-26 12:15:00
6 | 5 | 1 | Hi :D | 0 | 2016-03-26 15:00:00
首先按date
排序,然后按user_id
分组
SELECT *
FROM
(
SELECT * from messages
WHERE `user_id`=1 or `recipient_id`=1
ORDER BY `date` DESC
) m
GROUP BY `user_id`
结果:
3 | 1 | 3 | Are you there? I want meet you! :P | 0 | 2016-03-26 12:20:00
5 | 2 | 1 | Fine :) | 0 | 2016-03-26 12:15:00
6 | 5 | 1 | Hi :D | 0 | 2016-03-26 15:00:00
sqlFiddle演示
我希望我能比我的评论中解释得更好,如下所示:
首先,您需要有日志记录系统,因此当用户登录时,您将其ID保存到会话中。
第二步,根据登录的用户,您可以显示他与其他用户在"收件箱"中的所有消息,从user_id=$_SESSION['id']的消息中选择*,您可以加入表并显示用户和收件人中的名称
第三个当他点击与其他人的聊天时,你会显示他们之间的所有消息,根据用户id和receivent_id 按日期排序
像select * from messages group by user_id order by date
这样的查询会对您进行排序吗?Fiddle演示。