0

I'm creating a message system in PHP but I’m having a bit of a problem with the MySQL query. I want a simple and clean solution that is easy to understand. The query should sort the messages into conversations and then display the username + latest message. There are a few solutions here on the forum but they are all long and complicated and I have a hard time understanding them.

I have the database setup like this:

(messages) id, messages, sentby, sentto

And my query looks like this:

SELECT m1.*
  FROM messages m1 
  LEFT 
  JOIN messages m2 
    ON m1.sentby => m2.sentby 
   AND m1.id < m2.id
 WHERE m2.id IS NULL 
   AND m1.sentto = ?

The query sorts the messages into conversations and displays them in a good order but the last messages on each conversation is not displayed. All the messages have an id so the latest message should be displayed if I sort them by id?

I am one of many beginners here so be friendly and describe the solutions in detail. All the help I can get is appreciated!

Tobias
  • 13
  • 1
  • 6
  • I'm afraid the solution might be too long and complicated :-( – Strawberry Nov 27 '16 at 12:38
  • @Strawberry Thats sad.. I saw that you marked it as dublicate. Could you give me a example with my DB setup? The user in the other thread sort them by home? Could I do the same with my id? I also think it would be a good idea to explain how this query should look like for beginners like me. – Tobias Nov 27 '16 at 13:02
  • @Strawberry Please help, I have spent days on just this query.. – Tobias Nov 27 '16 at 13:25
  • If the existing answers fail to address your question, explain why AND see http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Nov 27 '16 at 22:10
  • @Strawberry I think I need to study Mysql a litle bit deeper, until then I just change my DB structure. Thanks anyway! – Tobias Nov 28 '16 at 14:22

0 Answers0