There is a table of messages in the database. The structure is as follows:

CREATE TABLE `message` ( `ID_message` bigint(20) NOT NULL auto_increment, `ID_user` bigint(20) NOT NULL, `ID_user_to` bigint(20) NOT NULL, `created_at` datetime NOT NULL, `is_read_at` datetime default NULL, `text` text, PRIMARY KEY (`ID_message`) )

It is required to receive for the current user all last correspondence. Let the Id of the user who entered “My Messages” = 5. There are 5 entries in the Database with the following id:

ID_user = 5 ID_user_to = 2 created_at = 01.01.2000
ID_user = 5 ID_user_to = 2 created_at = 02.01.2000
ID_user = 3 ID_user_to = 5 created_at = 01/01/2000
ID_user = 2 ID_user_to = 5 created_at = 01/03/2000
ID_user = 5 ID_user_to = 3 created_at = 1/7/2000

So the user should see two lines:
Correspondence with user ID = 3 Last message: 1/7/2000, message text
Correspondence with user ID = 2 Last Post: 1/3/2000, message text

I made a request:

 SELECT ID_user,ID_user_to, MAX(created_at) FROM `message` WHERE ID_user = 5 OR ID_user_to = 5 GROUP BY ID_user,ID_user_to ORDER BY created_at DESC

In this example, it will return the strings:

ID_user = 5 ID_user_to = 3 created_at = 1/7/2000
ID_user = 2 ID_user_to = 5 created_at = 01/03/2000
ID_user = 5 ID_user_to = 2 created_at = 02.01.2000
ID_user = 3 ID_user_to = 5 created_at = 01/01/2000

How to make it so that instead it displays:
ID_user = 5 ID_user_to = 3 created_at = 1/7/2000
ID_user = 2 ID_user_to = 5 created_at = 01/03/2000?

After all, messages from id = 5 for id = 2 and from id = 2 for id = 5 refer to the same correspondence.

  • maybe you will allow to bring your question to a normal view? - Specter
  • That's better? - Sergey133

2 answers 2

  1. Give the entire structure of the table.
  2. Ask the question accurately and briefly - or rather, provide a piece of data in the table and what you want to receive as a result of the query.

After all, messages from id = 5 for id = 2 and from id = 2 for id = 5 refer to the same correspondence. That's what I expected from you. You need to make one more field - which will indicate that the message belongs to a certain correspondence - and as soon as you do - everything will fall into place

  • Redid .. - Sergey133

Dizzy while reading the question) If I understood correctly, you need to specify in the request

ID_user != 5

And now the amendment:

 Значит пользователь должен увидеть две строки: this -> Переписка с пользователем ID=3 Последнее сообщение: 07.01.2000, текст сообщения Переписка с пользователем ID=2 Последнее сообщение: 03.01.2000, текст сообщения

AND

 Как сделать так, чтобы вместо этого он выводил: and this -> ID_user = 5 ID_user_to = 3 created_at = 07.01.2000 ID_user = 2 ID_user_to = 5 created_at = 03.01.2000 ?

During the question, you indicate in two places different IDs in the desired result, which is very confusing for the reader of the question!

  • You do not understand, I guess. ID_user - author, ID_user_to - addressee. ID_user = 5 ID_user_to = 3 created_at = 07.01.2000 - this will be the last message of correspondence with user 3 ID_user = 2 ID_user_to = 5 created_at = 03.01.2000 - this will be the last message of correspondence with user 2 The last message of my correspondence with another user can be as incoming for me as well as outgoing ... - Sergey133