Search code examples
phpmysqlmessaging

Messaging System with PHP/MySQL

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
Solution 
  • 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