Threaded messaging in MySQL and PHP question

Question

thanks for helping me out.

I'm creating a threaded messaging system using MySQL and PHP, and returning the results in XML. I'm looking to create a system that threads messages in a similar way to the iPhone SMS system - where there is only 1 thread between users, and the threads with the newest content appear at the top (regardless of whether the last message was sent or received).

My simple idea is to have a base page called "threads", which is something of the inbox. I'd like to pull all the distinct threads (and the last message sent or received) here. When you click into a thread, I'd query the database for all the messages/replies between those users.

My database structure:

MESSAGES: id | senderid | recipients | body | time

I have one huge questions/problems:

I can't do a straight query where recipientid=myid because the user could have sent a message but never received a reply, and in that use case the unanswered message should still appear (in chronological order) in the "inbox". But if I select messages where both senderid=myid OR recipientid=myid I get two threads as the query comes across both a sent and received message between the same two users. How do I return the first unique communication between two users - rather than the first unique received message AND the first unique sent message?
I use a LEFT JOIN to connect the messages table to the users table, which has the user's name. I just them ON messages.senderid= users.name. This works perfectly for threads where the current user is the last to receive a message. But for some threads, those where the current user has sent the only/last message - the above LEFT JOIN will return the current users name, rather than the name of the user he sent the message to. How can I dynamically write something like "if($userid == senderid) { LEFT JOIN users ON users.id = messages.recipientid; } else { LEFT JOIN users ON users.id = senderid}?

Thank you!

This is not quite an answer as I don't really understand how iPhone SMS works, but according to your description, I might solve the problem like that: You group senderid and recipientid together. When you query for a message, both does not make difference — TheOnly92
– TheOnly92, Commented Apr 11, 2011 at 4:18
Can you group two columns together in MySQL? That would be a perfect solution. Something like SELECT senderid, recipientid WHERE group(senderid,recipientid) = $userid. — Walker
– Walker, Commented Apr 11, 2011 at 4:30

Emil Vikström · Accepted Answer · 2011-04-11 05:23:18Z

1

Maybe something like this?

WHERE
  (senderid = $otheruser
   AND recipientid = $currentuser)
  OR
  (senderid = $currentuser
   AND recipientid = $otheruser)

answered Apr 11, 2011 at 5:23

Emil Vikström

92.3k17 gold badges144 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Threaded messaging in MySQL and PHP question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related