Private Message Group By Conversation

[ShoeLace1291]

Hello, all!  I am trying to write my own private messaging chat script for my website.  I would like the "Inbox" to display the "conversations" with other users, but only by the last message of the conversation.  I found a thread on stackoverflow and used a query suggested on that page.  The query doesn't find any rows.  http://stackoverflow.com/questions/10520246/mysql-query-to-group-messages-into-a-conversation-and-show-the-first-in-the-inbo

 

$query_convos = "
            SELECT m1.message_id, m1.author_id, m1.recipient_id, m1.content, UNIX_TIMESTAMP(m1.date_sent)
            FROM private_messages AS m1
            WHERE m1.date_sent = (
                SELECT max(m1.date_sent)
                FROM private_messages AS m2
                WHERE m1.author_id = m2.author_id
                    AND m1.recipient_id = m2.recipient_id
                    AND (author_id = ".$this->user['id']." OR recipient_id = ".$this->user['id'].")        
            )
            GROUP BY m1.author_id, m1.recipient_id
            ORDER BY m1.message_id DESC
            LIMIT 10
            ";

Edited February 23, 2014 by ShoeLace1291

[requinix]

It would be so much easier if you just attached a "conversation ID" to each message. Which you know when the messages are created: creating a new message is a new conversation, while hitting Reply would be continuing an existing conversation.

That ID can very easily be just the ID of the original message; when you make a new message in the conversation you copy the ID from the message being replied to, if present, or use that message's own ID, if not.