title: Displaying message status: read and unread (differs from other users) in PHP/MySQL messaging system
body: <p>I am displaying the message status that differs from each user. Let's say <code>user1</code> sends a message to <code>user2</code>, <code>user1</code>'s message status then sets to <code>read</code>, while <code>user2</code>'s message is set to <code>unread</code> by default. It will be updated after <code>user2</code> clicks the message.</p>
  
  <p>So in these scenario, the message of <code>user1</code> (from the inbox) will have a gray-colored font which indicates that the message is set to <code>read</code> (since <code>user1</code> is the one who is sending). On the other side, <code>user2</code> have a bold font that indicates that the message is <code>unread</code>.</p>
  
  <p>Here is the first structure of the table: </p>
  
  <pre><code>message(messageid, fromid, toid, message, timestamp, status)
  </code></pre>
  
  <p>The problem here is that if I update the message status to <code>read</code>, it affects the other side (<code>user2</code>). So I add another column that will set the status differently from <code>user1</code> and <code>user2</code>:</p>
  
  <pre><code>message(messageid, fromid, toid, message, timestamp, from_status, to_status)
  </code></pre>
  
  <p>Here, <code>from_status</code> is for the <code>fromid</code> and <code>to_status</code> is for <code>toid</code>. But I'm having a problem on how to use these values to display the status.</p>
  
  <p>The PHP code of that I use during my first attempt is these:</p>
  
  <pre><code>&lt;?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&lt;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
    }
  }
  ?&gt;
  </code></pre>
  
  <p>(The query fetches each conversation from every user with the latest conversation.)</p>
  
  <p>These code works fine for the main user, which is <code>user1</code>, but affects the other side, which views that the message received from <code>user2</code> is set to <code>read</code> instead or <code>unread</code>.</p>
  
  <p>So, I'm having some trouble on what to do on the modified table, having 2 separate <code>status</code> each for each user. How can I get these done?</p>