CODEWITHSUNDEEP
mysql
Darius
Darius

Reputation: 1664

Messaging system MYSQL, issue with getting latest reply

I have a table with

message_id, to_id, from_id, message

I made a threaded messaging system. I have 5 people talking to 1 member.

As the member I see

Member 1 says hi, member 2 says hello ( I want this to say latest reply was hello )

Member 1 says hi, member 3 says hi, member 1 says welcome ( I want this to say welcome )

member 2 says hi (I want this to say hi )

Now, I'm only provided with one of the ID's which is the sessionid of the member1. How would I go about showing scenario 2 where I (member1) have left the latest reply.

I can easily do 

 SELECT * FROM messages WHERE to_id=$session_id ORDER BY message_id DESC LIMIT 1

but that would only show the INCOMING messages. I need to show the LAST of the incoming AND outgoing messages.

What query would you suggest? :\ Maybe an alteration to the table if there's no efficient way? First time i've ever setup a messaging system, advice is welcome.

Upvotes: 0

Views: 116

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

You would need to use the UNION operator to retrieve the latest incoming message, and tack onto that result the latest outgoing message:

SELECT * FROM messages WHERE to_id=$session_id ORDER BY message_id DESC LIMIT 1

UNION ALL

SELECT * FROM messages WHERE from_id=$session_id ORDER BY message_id DESC LIMIT 1

Upvotes: 1

Braiba
Braiba

Reputation: 571

SELECT 
*
FROM messages 
WHERE to_id = $session_id
OR from_id = $session_id
ORDER BY message_id DESC
LIMIT 1

Upvotes: 1

Related Questions