0

I want to create a message system where users can send messages to each other. It should be thread-based like in Facebook. So every user should be in only one specific message-thread with another single user.

The main overview of all messages isn't working as I want it to be. It only shows the oldest (first) message text and date of a thread and doesn't sort by latest message sent.

This is my SELECT-Statement and I think I need a sub-query somewhere to let it sort by the latest date and text, but I'm not familiar with that and also don't know where to put that and how?

So basically I just want that the message threads are sorted by the latest message received, showing the latest text and date of a specific thread.

    $menu = mysql_query("SELECT 
t.`id`, t.`creator_uid`,
c.`uid` AS content_uid, c.`text`, c.`date` AS content_date,
a.`status`, a.`date` AS assign_date,
CASE 
WHEN t.`creator_uid`='".sInput($_SESSION['uid'])."' THEN `receiver_uid`
WHEN t.`receiver_uid`='".sInput($_SESSION['uid'])."' THEN `creator_uid`
END AS uid
FROM `messages_content` c
LEFT JOIN `messages_thread` t ON t.`id` = c.`thread_id`
LEFT JOIN `messages_assign` a ON a.`id` = t.`id`
WHERE 
t.`creator_uid`='".sInput($_SESSION['uid'])."' 
OR 
t.`receiver_uid`='".sInput($_SESSION['uid'])."' 
GROUP BY t.`id` 
ORDER BY c.`id` DESC") or die (mysql_error());

MySQL database structure:

messages_assign: id,uid,thread_id,status,date

messages_content: id,uid,thread_id,text,date

messages_thread: id,creator_uid,receiver_uid

1 Answers1

0

You need to join with a subquery that does:

SELECT select thread_id, MAX(date) as latest_post_date
FROM messages_contents
GROUP BY thread_id

and then sort by latest_post_date

$menu = mysql_query("SELECT 
    t.`id`, t.`creator_uid`,
    c.`uid` AS content_uid, c.`text`, c.`date` AS content_date,
    a.`status`, a.`date` AS assign_date,
    CASE 
    WHEN t.`creator_uid`='".sInput($_SESSION['uid'])."' THEN `receiver_uid`
    WHEN t.`receiver_uid`='".sInput($_SESSION['uid'])."' THEN `creator_uid`
    END AS uid
FROM `messages_content` c
LEFT JOIN `messages_thread` t ON t.`id` = c.`thread_id`
LEFT JOIN `messages_assign` a ON a.`id` = t.`id`
LEFT JOIN (SELECT thread_id, MAX(date) AS latest_post_date
            FROM messages_contents
            GROUP BY thread_id) AS m ON t.id = m.thread_id
WHERE 
t.`creator_uid`='".sInput($_SESSION['uid'])."' 
OR 
t.`receiver_uid`='".sInput($_SESSION['uid'])."' 
GROUP BY t.`id` 
ORDER BY m.latest_post_date DESC") or die (mysql_error());
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you, it seems to work this way ordering it correctly, but it doesn't show the latest text of a message-thread. I tried to add `text` in the sub-query, but then it shows no text at all. Do I need another sub-query for the latest text that has been sent? –  Sep 10 '15 at 22:24
  • I thought you just wanted to order by the latest message time, not show the latest message. See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the entire row with the latest date. – Barmar Sep 10 '15 at 23:08