Hi I'm trying to make a messaging system with php and mysql.
The mysql table is simple: id sender receiver text timestamp
I'm trying to make the messaging somewhat like Facebook/Twitter so the list is in 'conversations' and the last message in the conversation is viewed.
This is what I have atm:
(SELECT * FROM messages WHERE receiver = 13 OR sender = 13 GROUP BY receiver,sender ORDER BY id ASC) ORDER BY id ASC
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
what you need is a unique conversation id between the chat-partners. i've simulated this with the subquery, hope this helps
Use DESC for fetch new rows, default is ASC
(SELECT * FROM messages WHERE receiver = 13 OR sender = 13 GROUP BY receiver,sender ORDER BY id DESC)
AND SET LIMIT 1 ,1 AFTER ORDER BY
I think you need to
(receiver = '{receiver id}' AND sender = '{sender id}' ) OR (receiver ='{sender id}' AND sender = '{receiver id}' )
receiver And sender.But my answer is ok!receiver = 13 OR sender = 13 to receiver = 13 UPDAte: I'm not sure if it works perfect:
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
to reverse the order:
ORDER BY timestamp DESC
