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