CODEWITHSUNDEEP
phpmysqlsorting
lazycrazyengineer
lazycrazyengineer

Reputation: 18

mysql sorting messages sent and received

I have a table in my database

SenderId    ReceiverId  Message         DateAndTime
int(11)     int(11)     varchar(200)    DATETIME

I want to sort all messages for a logged user where in conversation style. Messages between logged user say UserA and UserB, UserC should appear like this:

UserA   UserB   Sent
UserA   UserB   Sent
UserB   UserA   Received

UserA   UserC   Sent
UserC   UserA   Received
UserA   UserC   Sent

UserD   UserA   Receied
UserA   UserD   Sent

I have tried this query but result is not as i expect

SELECT * FROM messages WHERE SenderId like ? or ReceiverId like ? order by senderId, dateAndDate;

Upvotes: 0

Views: 49

Answers (2)

user2009750
user2009750

Reputation: 3197

This should do the trick.

SELECT SenderId, ReceiverId, Message, DateAndTime, CASE WHEN SenderId=1 THEN ReceiverId Else SenderId END as Optional from messages where SenderId = 1 or ReceiverId=1 order by Optional, DateAndTime desc;

Assuming Logged UserID is 1, use place holder instead.

Upvotes: 0

AgRizzo
AgRizzo

Reputation: 5271

This may work. It doesn't sort conversation by time, but by participants.

SELECT CONCAT(LEAST(SenderID, ReceiverId), '-', GREATEST(SenderID, ReceiverId)) AS conversation
  , SenderID, ReceiverID, Message, DateAndTime
FROM messages
WHERE SenderID = ? OR ReceiverId =?
ORDER BY conversation, DateAndTime DESC;

Upvotes: 1

Related Questions