Query (Inbox and Outbox, etc) for Private Messaging System - PHP and MySQL -

i'm writing private messaging system website. user can communicate 1 of more other users (including himself / herself). have 3 tables

- users - conversation_list - conversation_messages 

for database shown below.

the users table holds users

the conversation_messages table holds messages written, each message has conversation_id

the conversation_list holds list of participants in each conversation

each conversation of course, identified unique conversation_id

now, wish query following tables:

- getinboxmessages <-- difficult     new messages directed user.      replies message should grouped conversation      , latest reply previewed - getoutboxmessages     messages sent user.      replies messages should grouped conversation      , latest reply previewed - getconversation     each message replies - isunreadmessage     check if message has been read or not - getnumberofunreadmessages     number of unread messages 

what have done far shown below.

getoutboxmessages, getconversation, isunreadmessage , getnumberofunreadmessages work, although queries may not optimal! have real trouble getting getinboxmessages.

i can first thread of conversation in inbox. other threads (replies) not aplear new messages, rather sent messages (outbox). however, reply message (part of conversation) not appear new message!

an ideas? support in getting right query getinboxmessages optimizing queries heartily welcome!

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; 

queries

getinboxmessages ================ select * conversation_messages m join     (select mx.conversation_id,      max(mx.date_created) maxtime     conversation_messages mx     group mx.conversation_id) mx on m.conversation_id = mx.conversation_id     , m.date_created = mx.maxtime join     (select mu.conversation_id      conversation_list mu      mu.user_id = :subuser      group mu.conversation_id) mux on m.conversation_id = mux.conversation_id join conversation_list mu on m.conversation_id = mu.conversation_id group mu.conversation_id order m.date_created desc  getouboxmessages ================ select * conversation_messages m  join     (select mx.conversation_id,      max(mx.date_created) maxtime      conversation_messages mx      group mx.conversation_id) mx on m.conversation_id = mx.conversation_id , m.date_created = mx.maxtime  join     (select mu.conversation_id, mu.user_id      conversation_list mu      mu.added_by = :subuser      group mu.conversation_id) mux on m.conversation_id = mux.conversation_id  join conversation_list mu on m.conversation_id = mu.conversation_id  order m.date_created desc  isunreadmessage =============== select * conversation_list date_created >= date_lastview , conversation_id = :messageid , user_id = :subuser  getconversation =============== select conversation_id, message, sender, date_created  conversation_messages  conversation_id = :submessage_id  order date_created desc  getnumberofunreadmessages ========================= select l.conversation_id, count(*)   conversation_list l join   conversation_messages m on m.conversation_id = l.conversation_id , m.date_created >= l.date_lastview  l.user_id = :subuser group l.conversation_id