Hans Martin Hans Martin - 4 months ago 12
MySQL Question

get last Conversation

I need your help!

Problem: I have a message system between users and I need to get the last conversation of all conversations. This means, I have to get the last written message and the conversation with probably sooner message between user1 and user2.

My 'message' table structure is this:

'id' | 'uidfrom' | 'uidto' | 'content' | 'cdate'


enter image description here

'uidfrom' is the user who wrote the message and 'uidto' is the user who received the message.

Do you guys have an idea or is it even possible, to catch the last conversation? So in this case the last written message is 'id' = 5 and now I need to get the whole conversation between 'uidfrom' = 1 AND 'uidto' = 2 + 'uidfrom' = 2 AND 'uidto' = 1.

Answer
Select *
from message
where
  uidfrom IN (select uidfrom,uidto from message order by id desc limit 0,1) AND
  uidto IN (select uidfrom,uidto from message order by id desc limit 0,1)