Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Private chat system MYSQL query to display last message of sender/receiver
- message_id|sender_id||receiver_id|message_text| created_time
- 1 101 102 Message A 2012-06-07 08:07:18
- 2 101 102 Message B 2012-06-07 08:10:20
- 3 103 102 Message C 2012-06-07 08:12:43
- id | name
- 101 bob
- 102 jack
- 103 mark
- name|message_text | created_time
- mark message C 2012-06-07 08:12:43
- bob message B 2012-06-07 08:10:20
- SELECT * FROM messages,users
- WHERE messages.receiver_id = 102
- AND messages.sender_id=users.id
- AND messages.created_time IN
- (SELECT MAX(created_time)
- FROM messages
- GROUP BY sender_id)
- ORDER BY messages.created_time DESC
- name|message_text | created_time
- mark message D 2012-06-07 08:12:48
- bob message B 2012-06-07 08:10:20
- SELECT users.name,
- temp.MESSAGE_TEXT,
- temp.created_time
- FROM
- (SELECT sender_id, MESSAGE_TEXT, created_time
- FROM messages
- WHERE created_time IN
- (SELECT MAX(created_time)
- FROM messages
- GROUP BY sender_id)) AS temp
- LEFT JOIN users ON temp.sender_id = users.id;
Add Comment
Please, Sign In to add comment