Databases
StackOverflow2013
Postsdbo
POPrivate chat system MYSQL query to display last message of sender/receiver
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPrivate chat system MYSQL query to display last message of sender/receiver
    text
    copied!<p>Here I am extending my previous question.</p> <p><a href="https://stackoverflow.com/questions/10929366/private-chat-system-mysql-query-orderby-and-groupby">Private chat system MYSQL query ORDERBY and GROUPBY</a></p> <p>In addition, I created one more table called users from where I will get users info.</p> <p>messages table</p> <pre><code> message_id|sender_id||receiver_id|message_text| created_time 1 101 102 Message A 2012-06-07 08:07:18 2 101 102 Message B 2012-06-07 08:10:20 3 103 102 Message C 2012-06-07 08:12:43 </code></pre> <p>users table:</p> <pre><code> id | name 101 bob 102 jack 103 mark </code></pre> <p>Now finally I am able to get following results:</p> <pre><code> name|message_text | created_time mark message C 2012-06-07 08:12:43 bob message B 2012-06-07 08:10:20 </code></pre> <p>By using this query:</p> <pre><code>SELECT * FROM messages,users WHERE messages.receiver_id = 102 AND messages.sender_id=users.id AND messages.created_time IN (SELECT MAX(created_time) FROM messages GROUP BY sender_id) ORDER BY messages.created_time DESC </code></pre> <p>Now what I want in my result is </p> <p>if jack(id:102) reply to mark(id:103) then how can I get this output:</p> <pre><code> name|message_text | created_time mark message D 2012-06-07 08:12:48 bob message B 2012-06-07 08:10:20 </code></pre> <p>NOTE:HERE "MESSAGE D" AND TIME STAMP IS OF JACK WHICH HE REPLIED TO MARK. </p> <p>where message_text field will display the last message between mark and jack created_time field will show message created time and name field will show the name of person to whom jack is sending or receiving messages. </p> <p>I am thinking we need to modify/split tables but don't know how and what query will do this task.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload