PHP/MySQL SELECT Statement - Slight Issue

Question

Hello all,

I have recently found a hole in my SELECT statement for a private messaging system that I am developing.

Here's the basic structure of my table:

tblpm:
unique_id    thread_id    subject    content    receiver_id    sender_id    date_sent

The page I am developing is the inbox, where all most recent messages are displayed for a user.

The SELECT statement I am using is:

$data = mysql_query("
SELECT tblpm.* FROM tblpm 
WHERE date_sent 
IN(
   SELECT MAX(date_sent)
   FROM tblpm 
   GROUP BY message_id
  )
AND receiver_id ='$usrID' 
ORDER BY id DESC") or die(mysql_error());

Now, here's what I have noticed: When I first send a message to the user, it sends fine, and does not show up in the inbox. (Because it shouldn't, it should show up in "sent items"). Then, if a user were to "reply" to the message (thus, the same thread), it would also show up fine in the inbox.

However, if I reply to "the reply" (in other words, the third message in the thread), it does not show up in the inbox anymore, because the SELECT statment is directed to choose the MAX(date_sent) of a thread WHERE the receiver_ID = $usrID. Well, the issue is that the most recent (date_sent) of a threaded item is being "received" by someone else, so as a result, the SELECT statement does not display 'any' of the threaded items.

Hopefully this makes sense.

Here's a visual representation of what's happening:

unique_id    thread_id    subject   receiver_id    sender_id     date_sent
   1            144         Msg         22            33         2009-07-22 //Will display fine in sent items.
   2            144         re: Msg     33            22         2009-07-23 //Will display fine in inbox of user (usrID 33).
   3            144         RE: re:Msg  22            33         2009-07-24 //Once this message is sent, the entire thread (thread_id 144) no longer displays in the inbox.

Any help on this would be much appreciated!

Answer 1

Seems like this should do what you want:

$data = mysql_query("
SELECT tblpm.* FROM tblpm 
WHERE date_sent 
IN(
   SELECT MAX(date_sent)
   FROM tblpm 
   WHERE receiver_id ='$usrID' 
   GROUP BY message_id
  )
AND receiver_id ='$usrID' 
ORDER BY id DESC") or die(mysql_error());