Need a solution for mysql select data where until

I have a message conversation between two people, user 222 and User 5555, it is displayed like this:

Conversation-ID 1, Message-ID 5, User-ID 222: "Oh, and I'm happy about that!"
Conversation-ID 1, Message-ID 4, User-ID 222: "And bought me a new car."
Conversation-ID 1, Message-ID 3, User-ID 222: "I just won some money!"
Conversation-ID 1, Message-ID 2, User-ID 5555: "Fine, how are you?"
Conversation-ID 1, Message-ID 1, User-ID 222: "Hi there, how are you?"

Now I am looking for a solution in PHP / MySQL to display only the messages from User-ID 222 until the message from User-ID 5555.

The result should be:

Conversation-ID 1, Message-ID 5, User-ID 222: "Oh, and I'm happy about that!"
Conversation-ID 1, Message-ID 4, User-ID 222: "And bought me a new car."
Conversation-ID 1, Message-ID 3, User-ID 222: "I just won some money!"

Actually I have

"SELECT * FROM table_conversations WHERE conversation_id='1' and user_id !='5555' ORDER BY message_id DESC"

but of course this will display me all messages from User-ID 222. What I want are only the last unanswered messages from User-ID 222.

Is there any command or solution in PHP & MySQL to get this result, also working for all other conversations?

Answer

Assuming that the message id specifies the ordering of the messages:

select *
from table_conversation c
where user_id = 222 and
      message_id > (select max(message_id) from table_conversation c2 where user_d = 555);

Advertisement

Answer