title: Query (Inbox and Outbox, etc) for Private Messaging System - PHP and MySQL
body: <p>I'm writing a private messaging system for a website. A user can communicate with one of more other users (including himself / herself). I have three tables</p>
  
  <pre><code>- users
  - conversation_list
  - conversation_messages
  </code></pre>
  
  <p>for my database as shown below.</p>
  
  <p>The <code>users</code> table holds all users</p>
  
  <p>The <code>conversation_messages</code> table holds the messages written, each message has a conversation_id</p>
  
  <p>The <code>conversation_list</code> holds the list of participants in each conversation </p>
  
  <p>Each conversation is of course, identified by the unique <code>conversation_id</code></p>
  
  <p>Now, I wish to query the following from the tables:  </p>
  
  <pre><code>- getInboxMessages &lt;-- Difficult
      Get all new messages directed to a user. 
      Replies to a message should be grouped as a conversation 
      and the latest reply previewed
  - getOutboxMessages
      Get all messages sent by the user. 
      Replies to messages should be grouped as a conversation 
      and only the latest reply previewed
  - getConversation
      Each message with all replies
  - isUnreadMessage
      Check if a message has been read or not
  - getNumberOfUnreadMessages
      Get the number of unread messages
  </code></pre>
  
  <p>What I have done so far is shown below.</p>
  
  <p><code>getOutboxMessages</code>, <code>getConversation</code>, <code>isUnreadMessage</code> and <code>getNumberOfUnreadMessages</code> do work, although the queries may not be optimal! I have real trouble getting the <code>getInboxMessages</code>. </p>
  
  <p>I can only get the first thread of a conversation in the inbox. Any other threads (replies) do not aplear as new messages, but rather as sent messages (outbox). 
  However, any reply to the message (part of a conversation) does not appear as a new message! </p>
  
  <p>An ideas? Any support in getting the right query for getInboxMessages
  as well as optimizing the queries is heartily welcome!</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `conversation_list` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` varchar(50) NOT NULL,
    `conversation_id` int(11) NOT NULL,
    `added_by` varchar(50) NOT NULL,
    `date_created` int(11) NOT NULL,
    `date_lastPost` int(11) NOT NULL,
    `date_lastView` int(11) NOT NULL,
    `status` tinyint(1) NOT NULL,
    UNIQUE KEY `id` (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  CREATE TABLE IF NOT EXISTS `conversation_messages` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sender` varchar(50) DEFAULT NULL,
    `conversation_id` int(11) NOT NULL,
    `message` text NOT NULL,
    `date_created` int(11) NOT NULL,
    `status` tinyint(1) NOT NULL,
    UNIQUE KEY `id` (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  
  CREATE TABLE IF NOT EXISTS `users` (
    `username` varchar(30) NOT NULL,
    `password` varchar(40) default NULL,
    `usersalt` varchar(8) NOT NULL,
    `userid` varchar(32) default NULL,
    `userlevel` tinyint(1) unsigned NOT NULL,
    `email` varchar(50) default NULL,
    `timestamp` int(11) unsigned NOT NULL,
    `regdate` int(11) unsigned NOT NULL,
    PRIMARY KEY  (`username`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>QUERIES</p>
  
  <pre><code>getInboxMessages
  ================
  SELECT *
  FROM conversation_messages AS m
  JOIN
      (SELECT mx.conversation_id,
       MAX(mx.date_created) AS MaxTime
      FROM conversation_messages AS mx
      GROUP BY mx.conversation_id) AS mx ON m.conversation_id = mx.conversation_id
      AND m.date_created = mx.MaxTime
  JOIN
      (SELECT mu.conversation_id
       FROM conversation_list AS mu
       WHERE mu.user_id = :subuser
       GROUP BY mu.conversation_id) AS mux ON m.conversation_id = mux.conversation_id
  JOIN conversation_list AS mu ON m.conversation_id = mu.conversation_id
  GROUP BY mu.conversation_id
  ORDER BY m.date_created DESC
  
  getOuboxMessages
  ================
  SELECT *
  FROM conversation_messages AS m
  
  JOIN
      (SELECT mx.conversation_id,
       MAX(mx.date_created) AS MaxTime
       FROM conversation_messages AS mx
       GROUP BY mx.conversation_id) AS mx ON m.conversation_id = mx.conversation_id
  AND m.date_created = mx.MaxTime
  
  JOIN
      (SELECT mu.conversation_id, mu.user_id
       FROM conversation_list AS mu
       WHERE mu.added_by = :subuser
       GROUP BY mu.conversation_id) AS mux ON m.conversation_id = mux.conversation_id
  
  JOIN conversation_list AS mu ON m.conversation_id = mu.conversation_id
  
  ORDER BY m.date_created DESC
  
  isUnreadMessage
  ===============
  SELECT * FROM conversation_list WHERE date_created &gt;= date_lastView AND conversation_id = :messageid AND user_id = :subuser
  
  getConversation
  ===============
  SELECT conversation_id, message, sender, date_created 
  FROM conversation_messages 
  WHERE conversation_id = :submessage_id 
  ORDER BY date_created DESC
  
  getNumberOfUnreadMessages
  =========================
  SELECT l.conversation_id, count(*)
  FROM   conversation_list l
  JOIN   conversation_messages m ON m.conversation_id = l.conversation_id AND m.date_created &gt;= l.date_lastview
  WHERE  l.user_id = :subuser
  GROUP BY l.conversation_id
  </code></pre>