Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

  • MySql: Display last conversation between multiple users in Inbox

    @amuk.saxena
    Recommendations Offline Message

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.98k
    Comment on it

    Retrieve last message between multiple users and display it in inbox

     

    Hello friends today I am going to tell you how to display last conversation in Inbox. For example: In whatsapp chat list we can see the last conversation of each user. Here also we will display last conversation from the chat list.

    Below you can see the chat list of A with multiple users. This is the message table below.

     

    id from_user to_user message date_time
             
    1 A B Hello 2016-08-06 11:55
    2 B A Hi 2016-08-06 11:56
    3 A B How are you 2016-08-06 12:00
    4 B A I am good thanks 2016-08-06 12:05
    5 C A I am coming in 5 minutes 2016-08-06 12:15
    6 A C Ok 2016-08-06 12:16

     

    Instead of writing code for it and to make it much more easier I have added  one more column message_token, which will help to group last conversation by time. 

     

    id from_user to_user message message_token date_time
               
    1 A B Hello dsfdf343242435sdfsdfsd 2016-08-06 11:55
    2 B A Hi dsfdf343242435sdfsdfsd 2016-08-06 11:56
    3 A B How are you dsfdf343242435sdfsdfsd 2016-08-06 12:00
    4 B A I am good thanks dsfdf343242435sdfsdfsd 2016-08-06 12:05
    5 C A I am coming in 5 min. nbfgiuyewq6gf4534sdgh 2016-08-06 12:15
    6 A C Ok nbfgiuyewq6gf4534sdgh 2016-08-06 12:16

     

    Now you can get the last conversation easily by writing the simple query below:

     

    SELECT * FROM (SELECT message_token, MAX(created) AS created FROM messages GROUP BY message_token) AS x JOIN messages USING (message_token, created) where (from_user ='A' OR to_user = 'A') order by date_time desc

     

    Output of the query is:

     

    id from_user message message_token date_time
             
    4 B I am good thanks dsfdf343242435sdfsdfsd 2016-08-06 12:05
    6 A Ok nbfgiuyewq6gf4534sdgh 2016-08-06 12:16

     

     

     

    Thanks for reading

    mysql Display last conversation between two users
    Comment on it

 0 Comment(s)

Sign In
Forgot Password?
                           OR                           
Create an account !!
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: