CODEWITHSUNDEEP
mysql
sark9012
sark9012

Reputation: 5747

Reading messages from database in order

I am currently building a conversation style messaging system and need to assistance on the order of the messages being read from the database.

The database structure of message table in the database is as follows:

messageid | content | message_from | message_to | viewed | deleted | sent_date

I want to read the messages out in the order they are sent but not repeat the username if multiple messages are sent without a reply.

So if there were 3 message_from to the same message_to, they would be displayed and then any response would come after that. But as I said, if there were 3 messages from the same person without a response, I would display the username at first and then just content there after.

I have the following query which pulls out messages sent to the user.

SELECT f.username, t.username, m.content, m.sent_date FROM message m 
INNER JOIN user f ON f.userid = m.message_from
INNER JOIN user t ON t.userid = m.message_to
WHERE f.username = :username

I've tried my best to explain what I need, not sure how clear I have been!

Thanks.

Upvotes: 0

Views: 39

Answers (1)

JoLoCo
JoLoCo

Reputation: 1375

I'd make the decision on displaying the username in the display logic, rather than the database logic. (ie. in your HTML generation script)

To make sure the results are in the correct order, use ORDER BY m.sent_date ASC at the end of your SQL statement.

Upvotes: 1

Related Questions