Private chat system MYSQL query to display last message of sender/receiver

Question

Here I am extending my previous question.

Private chat system MYSQL query ORDERBY and GROUPBY

In addition, I created one more table called users from where I will get users info.

messages table

  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

users table:

 id  | name
 101   bob
 102   jack
 103   mark

Now finally I am able to get following results:

      name|message_text |  created_time
      mark   message C    2012-06-07 08:12:43
      bob    message B    2012-06-07 08:10:20

By using this query:

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

Now what I want in my result is

if jack(id:102) reply to mark(id:103) then how can I get this output:

      name|message_text |  created_time
      mark   message D    2012-06-07 08:12:48
      bob    message B    2012-06-07 08:10:20

NOTE:HERE "MESSAGE D" AND TIME STAMP IS OF JACK WHICH HE REPLIED TO MARK.

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.

I am thinking we need to modify/split tables but don't know how and what query will do this task.

and if `jack` replies to `mark` sender of the `message D` should be `jack` I guess? — Darshana, Jun 08 '12 at 04:50
@Jack what D R is saying is correct. If jack is replying then message D will be of jack. — Jadzia, Jun 08 '12 at 04:57
D R if you see chat system in sites like badoo.com and tagged.com. They are using the chat system what I am trying to do. — Jadzia, Jun 08 '12 at 05:20

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

What you're asking for is basically to group by thread rather than sender. Threads can be initiated between two people, regardless of being sender / receiver.

To accomplish this, I would recommend changing your data structure similar to what's been discussed here: Is there a simpler way to achieve this style of user messaging?

edited May 23 '17 at 11:59

Community

1
1

answered Jun 08 '12 at 05:21

Ja͢ck

170,779
38
263
309

Yess! I was looking for something like this. Let me try it..:) – Jadzia Jun 08 '12 at 05:29
all right jack so i created all tables as you said. Now what query will give me the result i wanted? – Jadzia Jun 08 '12 at 06:10
@Jadzia the thread is full of queries that should give you an idea of how to solve it. – Ja͢ck Jun 08 '12 at 06:14

score 0 · Answer 2 · edited Jun 08 '12 at 06:14

0

If this is a group chat don't limit to a receiver_id like messages.receiver_id = 102

SELECT users.name,
       temp.MESSAGE_TEXT,
       temp.created_time
FROM
  (SELECT sender_id, MESSAGE_TEXT, created_time
   FROM messages
   WHERE created_time IN
       (SELECT MAX(created_time)
        FROM messages
        GROUP BY sender_id)) AS temp
LEFT JOIN users ON temp.sender_id = users.id;

show last message each sender sent

edited Jun 08 '12 at 06:14

Ja͢ck

170,779
38
263
309

answered Jun 08 '12 at 06:06

Darshana

2,462
6
28
54

D R thanks again for reply. But this is off course limited to receiver_id. If you can have u look on what jack is saying. this i want but still query is problem there. – Jadzia Jun 08 '12 at 06:16
Actually query I am looking will be summary page of all messages. if you want i can make a demo graphics for u... – Jadzia Jun 08 '12 at 07:59
yes u better update your question. someone will answer. sorry! I'm little busy right now.. – Darshana Jun 08 '12 at 08:07

score 0 · Answer 3 · answered Aug 27 '23 at 09:10

json in table frist Tbl_meessage and table users

Tbl_meessage filds idmessage sender receiver message

table users filds id name family

use query and group by in delete message repeat

solution sender->id receiver->id or sender id

for example query select Tbl_message.sender=1 Tbl_receiver=2 or Tbl_message.sender=2 Tbl_receiver=1

full query sql

SELECT Tbl_meessage.idmessage,Tbl_meessage.sender,Tbl_meessage.receiver,Tbl_meessage.message,users.id,users.name,users.family FROM Tbl_meessage INNER JOIN users ON users.id=Tbl_meessage.receiver and Tbl_meessage.sender="+sender+" and Tbl_meessage.receiver="+receiver+" or Tbl_meessage.receiver="+sender+" and Tbl_meessage.sender="+receiver+" GROUP BY Tbl_meessage.idmessage

tip The user can see his own message but it may be with someone else's message sender. To solve this problem, the "or" command was used.

good luck.

Private chat system MYSQL query to display last message of sender/receiver

3 Answers3

Linked