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?

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