daily pastebin goal
90%
SHARE
TWEET

Untitled

a guest Sep 12th, 2018 58 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
  1. Private chat system MYSQL query to display last message of sender/receiver
  2. message_id|sender_id||receiver_id|message_text|   created_time
  3.     1         101          102       Message A    2012-06-07 08:07:18
  4.     2         101          102       Message B    2012-06-07 08:10:20
  5.     3         103          102       Message C    2012-06-07 08:12:43
  6.    
  7. id  | name
  8.  101   bob
  9.  102   jack
  10.  103   mark
  11.    
  12. name|message_text |  created_time
  13.       mark   message C    2012-06-07 08:12:43
  14.       bob    message B    2012-06-07 08:10:20
  15.    
  16. SELECT * FROM messages,users
  17. WHERE messages.receiver_id = 102
  18. AND messages.sender_id=users.id
  19. AND messages.created_time IN
  20. (SELECT MAX(created_time)
  21. FROM messages
  22. GROUP BY sender_id)
  23. ORDER BY messages.created_time DESC
  24.    
  25. name|message_text |  created_time
  26.       mark   message D    2012-06-07 08:12:48
  27.       bob    message B    2012-06-07 08:10:20
  28.    
  29. SELECT users.name,
  30.        temp.MESSAGE_TEXT,
  31.        temp.created_time
  32. FROM
  33.   (SELECT sender_id, MESSAGE_TEXT, created_time
  34.    FROM messages
  35.    WHERE created_time IN
  36.        (SELECT MAX(created_time)
  37.         FROM messages
  38.         GROUP BY sender_id)) AS temp
  39. LEFT JOIN users ON temp.sender_id = users.id;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top