CODEWITHSUNDEEP
phpmysqlsqlmessaging
Joris Blanc
Joris Blanc

Reputation: 601

Mysql php get last message in conversation related with user

I'm working on a mysql/php private messaging system, and I'm having a problem with the MYSQL code to use. I'm wanting to get the last message sent/received in a conversation between users. Like this page of facebook https://www.facebook.com/messages/. (It is not really easy to explain this with words.)

My mysql table for the messages is like this :

`id`            -> unique ID, autoincremented
`message_from`  -> sender user ID 
`message_to`    -> receiver user ID 
`message`       -> message
`date_created`  
`ip`            
`status`        -> read or not read

It is not clear in my head how to do it, so I have no initial script to show.

Upvotes: 2

Views: 5033

Answers (6)

Daniel Mabadeje
Daniel Mabadeje

Reputation: 51

This worked for me

    SELECT t1.*
        FROM messages AS t1
        INNER JOIN
        (
            SELECT
                LEAST(sender_id, receiver_id) AS sender_id,
                GREATEST(sender_id, receiver_id) AS receiver_id,
                MAX(id) AS max_id
            FROM messages
            GROUP BY
                LEAST(sender_id, receiver_id),
                GREATEST(sender_id, receiver_id)
        ) AS t2
            ON LEAST(t1.sender_id, t1.receiver_id) = t2.sender_id AND
               GREATEST(t1.sender_id, t1.receiver_id) = t2.receiver_id AND
               t1.id = t2.max_id
            WHERE t1.sender_id = :id OR t1.receiver_id = :id

where :id is binded.

Referenced from here https://laracasts.com/discuss/channels/laravel/get-the-latest-message-of-chat-model-with-mysql-just-cannot-get-the-idea-how-to-do-this

Upvotes: 0

John Max
John Max

Reputation: 432

You can use this 

$recup_messages = mysqli_query($connection, SELECT * FROM messages WHERE 

(message_from = $yourUserId  AND message_to = $yourOtherUserId) OR (message_from = $yourUserId  AND message_to = $yourOtherUserId ) ") or die(mysqli_error($connection));

while($affichage = mysqli_fetch_assoc($recup_messages))
{
$message_from = $affichage['message_from'];

$message = $affichage['message'];

echo "From : $message_from <br> $message <br><br>";

}

That will display your messages one after the other just like facebook. Now you have to style it the way you want and add information about date and other stuff

Upvotes: 0

Subhod30
Subhod30

Reputation: 488

SELECT f . * FROM ( SELECT * FROM message a WHERE ( LEAST( a.message_from, a.message_to ) , GREATEST( a.message_from, a.message_to ) , a.sent) IN (SELECT LEAST( b.message_from, b.message_to ) AS x, GREATEST( b.message_from, b.message_to ) AS y, MAX( b.date_created ) AS msg_time FROM message b GROUP BY x, y))f WHERE ".$mainUser." IN (f.message_from, f.message_to )ORDER BY f.date_created DESC limit $pageLimit,".$per_page

just copy this query and see in phpmyadmin

Upvotes: 1

Ja͢ck
Ja͢ck

Reputation: 173562

First you construct the query to get all conversations and the date of last message sent; a conversation is defined as the unique combination of sender and receiver.

SELECT message_from, message_to, MAX(date_created) AS max_date
FROM messages
WHERE message_from = :user OR message_to = :user
GROUP BY message_from, message_to

The :user is just a placeholder for the currently signed in user.

To get the actual data from the last message inside a conversation, you have to join the results back with the original table:

SELECT message, date_created, status
FROM messages
JOIN (
    SELECT message_from, message_to, MAX(date_created) AS max_date
    FROM messages m
    WHERE message_from = :user OR message_to = :user
    GROUP BY message_from, message_to
) AS lm ON m.date_created=lm.max_date AND m.message_from=lm.message_from AND m.message_to=lm.message_to

Upvotes: 1

fancyPants
fancyPants

Reputation: 51888

This will give you the latest message for each chat. Each chat is identified by the message_from and message_to ID.

SELECT
*
FROM messages m1
WHERE date_created = (SELECT MAX(m2.date_created) FROM messages m2 WHERE m1.message_from = m2.message_from AND m1.message_to = m2.message_to)

If you want to have a specific chat, just add a 

AND message_from = $yourUserId
AND message_to = $yourOtherUserId

Sorry, if this is not what you are looking for, I don't click on facebook links.

Upvotes: 2

Arion
Arion

Reputation: 31239

Maybe something like this:

SELECT
    *
FROM
    tblMessage
    JOIN(
            SELECT
                MAX(date_created) AS maxCreated,
                message_from
            FROM
                tblMessage
            GROUP BY
                message_from
        ) AS Latest
    ON tblMessage.date_created=Latest.maxCreated
    AND tblMessage.message_from=Latest.message_from

Upvotes: 0

Related Questions