There is such a table: id | id_from | id_for | message | message_status id | id_from | id_for | message | message_status id | id_from | id_for | message | message_status And there is such a request:

  SELECT m.message AS message, fu.username AS username, fu.img AS img, fu.id as id, m.message_status as status, m.id as idm FROM users AS fu LEFT JOIN user_message AS m ON m.id_for = fu.id WHERE m.id_from = "'.$_SESSION['id'].'" order by idm desc

Now he selects the first message in the chat between users, and I need the last. Ie message where id is more than previous messages enter image description here

    2 answers 2

    Do I understand correctly that you need information about a single message and its corresponding user? Do you have to solve the problem in one request? There will be one main and 3 correlated queries or JOIN. This is not always the most productive way - put everything in one request. Perform two queries - this is also not so much. First, retrieve all the recipients information.

     SELECT message AS message, id_for AS id_for, message_status as status, id as idm FROM user_message WHERE id_from = "'.$_SESSION['id'].'" ORDER BY m.id DESC

    In the loop, you put all the response information in the $ answers array, plus you additionally prepare the $ id_for array with user IDs. Next, perform the query

     SELECT * FROM user WHERE id IN ( ".implode(",", $id_for)." )

    Put the information about all users in the associative array $ users, in which the key is the user ID. Then, in a loop, create a table of answers $ answers, when you need to get information about the user, you access the $ users array

     $users[$answers['id_for']]
    • Now the last message is already outputting, but a new problem has appeared, it duplicates users (I uploaded an example picture) - Alexander Reizan
    • In the variant given in this answer, I suggest that you abandon the initial request and use two requests. - cheops
    • In general, I need to get from the database, photo, name, id, last message and status (whether the message is read) in order to place all this in a block, as in an example. And everything seems to be working, it remains to remove duplicates. Your option is easier to perceive, but I don’t know what to do next, how to put the whole thing into a cycle and pull out what is required? - Alexander Reizan
    • one
      There are two reasons. JOIN is not free, to create it you need to combine two tables into one large, the number of elements in which is equal to the product of the records of these tables. Those. if you have 100 entries in one table and 100 entries in another, there will be 10,000 in the intermediate table. In the case of LEFT JOIN, this is a bit wrong, plus there is optimization. However, the fact is that the larger the table, the more expensive the JOIN. In the case of two requests - there is no problem - in most cases they will be cheaper. - cheops
    • one
      The second argument: SQL was created in the 70s, when cars were expensive, and the people’s salary was worth nothing compared to their value. Now the situation is different - the machines are cheap, the time of the programmer is expensive. You are busy with the SQL query for a day or two, instead of filling the loop clear for both you and those who come after you. Large SQL sheets are not economically justified now - it is expensive to create and maintain them. - cheops

    You can do one of two ways:

    1. ... ORDER BY m.id desc LIMIT 1 ; - add to the end of the request

    2. Change the selection by message id like this: SELECT MAX(m.id) ....

    In both cases, you will need to remove the grouping by username, because it loses its meaning.

    • It turned out funny, checked in the console, id are displayed in descending order in a separate column, but the messages still do not display the last .. - Alexander Reizan
    • put the group by message, but now users repeat - Alexander Reizan