Displaying message status: read and unread (differs from other users) in PHP/MySQL messaging system

Question

I am displaying the message status that differs from each user. Let's say user1 sends a message to user2, user1's message status then sets to read, while user2's message is set to unread by default. It will be updated after user2 clicks the message.

So in these scenario, the message of user1 (from the inbox) will have a gray-colored font which indicates that the message is set to read (since user1 is the one who is sending). On the other side, user2 have a bold font that indicates that the message is unread.

Here is the first structure of the table:

message(messageid, fromid, toid, message, timestamp, status)

The problem here is that if I update the message status to read, it affects the other side (user2). So I add another column that will set the status differently from user1 and user2:

message(messageid, fromid, toid, message, timestamp, from_status, to_status)

Here, from_status is for the fromid and to_status is for toid. But I'm having a problem on how to use these values to display the status.

The PHP code of that I use during my first attempt is these:

<?php 
$id = $_SESSION['id'];
$query = mysql_query("SELECT m.* FROM message m
                                  LEFT JOIN message m2 ON (
                                  (m.fromid=m2.fromid AND m.toid=m2.toid) OR
                                  (m.fromid=m2.toid AND m.toid=m2.fromid)
                                  ) AND m.timestamp<m2.timestamp
                                  WHERE (m.fromid='$id' OR m.toid='$id') AND m2.toid IS NULL ORDER BY timestamp DESC");

while ($message = mysql_fetch_array($query)) {
  if ($message['status'] === 'unread') {
    // bold font style will be applied
  }
  else {
    // gray-colored font will be applied
  }
}
?>

(The query fetches each conversation from every user with the latest conversation.)

These code works fine for the main user, which is user1, but affects the other side, which views that the message received from user2 is set to read instead or unread.

So, I'm having some trouble on what to do on the modified table, having 2 separate status each for each user. How can I get these done?

Zachariahzacharias

Is there an option to re-set the status to unread? Because otherwise, you can just assume that if the person looking at the message is the sender, it'll be read, and have a single status field that tracks whether the recipient has read it. – Quotable

For a slightly better solution, rename the fields to sender_status and recipient_status, so it's clear which is which, and just update the appropriate one depending on who is reading the message right now – Quotable

For a more elegant solution, amend the database so that there's a new table for message recipients, with fields like 'usedID', 'status' and 'role', where role is 'sender' or recipient'. That will let you send the same message to multiple people, and track each of their statuses individually. – Quotable

@Quotable Would that reset the status of user2 too? – Zachariahzacharias

Not necessarily. So long as each status is stored separately, you can update them individually if you want to – Quotable

@Quotable I will keep the message table and add the message_recipients table? – Zachariahzacharias

Hi there. @GhostGambler removed the [solved] title tag for good reason, we don't do that here. I have additionally moved the appended answer to a wiki-style answer - if you wish to add your own answers please use the answer box in future. Also, if a high rep user edits your post, please contact them before reverting their edit - they may have knowledge about style and formatting that you do not. I have additionally removed the 'snippet' feature from your code blocks, since SQL/PHP won't run inside the browser. – Polystyrene

Answer 1

@andrewsi comment is quite nice, when you'll have for example many receivers. In your case it's only one additional field, so in my opinion it's not an overflow to use just one table. Regarding your case you can do this in one simple sql:

SELECT m.*,
    CASE 
        WHEN m.fromid = $id THEN m.from_status 
        WHEN m.toid = $id THEN m.to_status
    END as read_status
FROM message m
WHERE
    m.fromid = $id OR m.toid = $id
ORDER BY timestamp DESC;

And in your view you are only checking the read_status field

Hands

I kinda understand these now. I tested the query and gives me the message status. All I need is to implement it. Thanks. – Zachariahzacharias

I will implement it first, and I will mark it once I've make it work. You have my word. Do you mind upvoting my question to so it can reach other programmers who might encounter the same problem as mine? It will be a big help. :) – Zachariahzacharias

I have another question. These part only solves the portion of my problem. My inbox is grouped by the sender, the from, given from the query above on my php code. How do I modify these? Also it should display the latest conversation. – Zachariahzacharias

Oh, don't mind. I managed to solved it using some part of your code. You have my thanks mate. :) – Zachariahzacharias

Answer 2

(Posted on behalf of the OP.)

I've managed to solve the problem, thanks to @Rafal Mnich. So I grab a part of his query and do a bit of modification, and it works.

Here's the query:

SELECT m.msgid, m.fromid, m.toid, m.content,
  CASE
    WHEN m.fromid = '$id' THEN m.frommsgstatus
    WHEN m.toid = '$id' THEN m.tomsgstatus
  END AS msgstatus
FROM msg m
  LEFT JOIN msg m2
    ON ((m.fromid=m2.fromid AND m.toid=m2.toid) OR (m.fromid=m2.toid AND m.toid=m2.fromid)) AND m.timestamp<m2.timestamp
WHERE (m.fromid='$id' OR m.toid='$id') AND m2.toid IS NULL
ORDER BY m.timestamp DESC

These displays the messages grouped by the sender fromid, which also displays the latest conversation you have from each of the senders. And it displays the correct message status in both sides of the users.

Polystyrene

But why do you need this left join? – Legman

Hi @RafałMnich, you'll need to post that under the question after it is unlocked - I posted this on behalf of the OP, having taken the text from a solution appended to the question. – Polystyrene

@RafałMnich: the question is now unlocked, so you can post that under the question if you wish. – Polystyrene