There is such a block where users are displayed with whom I entered the correspondence

enter image description here

I want to display a photo, name and last message. Almost everything is fine, but in my case users are duplicated, as seen in the example of Katya, it appears 2 times. So it should not be.

This is what my query looks like:

$query = '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, m.id_for FROM users AS fu LEFT JOIN user_message AS m ON m.id_for = fu.user_id WHERE m.id_from = "'.$_SESSION['id'].'" order by idm desc '; $result = mysqli_query($link, $query); $num = mysqli_num_rows($result); $ar = array(); for($i=0;$i<$num;$i++) { $arr = mysqli_fetch_array($result); $ar[] = $arr;

I tried after the request, before throwing the data into the array, compare the id (they say, if there is one, then it is not necessary to upload it into the array), but for some unknown reason I did not succeed))

  • 3
    Get just a table last_messages , which update on the arrival of each message. And from it make a simple sample. And it works quickly, and it is not necessary to pore with requests. - etki
  • 2
    Perhaps I’ll say something stupid, but I don’t understand how you can get doubles with a left join. I would first of all look at the table of users - splash58
  • Duplicate, according to the user_message table, it consists of id, user_from, user_to, status and date - Alexander Reizan
  • WHERE ... GROUP BY id; - MolbOrg

1 answer 1

For example, 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, m.id_for FROM users AS fu LEFT JOIN user_message AS m ON m.id_for = fu.user_id WHERE m.id_from = "'.$_SESSION['id'].'" AND m.id = (SELECT MAX(m2.id) FROM message m2 WHERE m2.id_for = fu.user_id AND m2.id_from = "'.$_SESSION['id'].'")

An internal query is used to find the maximum id. The result will only get one message for the pair m.id_for, m.id_from.
Works since ancient times, when SQL is not yet overgrown with new features.

This option. I do not know whether in this case it is correct to refer from a subquery to fields from an external query ( id_for = fu.user_id ).

 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, m.id_for FROM users AS fu LEFT JOIN (SELECT id, message, id_for, id_from FROM user_message WHERE id_for = fu.user_id AND id_from = "'.$_SESSION['id'].'" ORDER BY id DESC LIMIT 1) AS m

Use join with a subquery that pulls out only the last message ( ORDER BY id DESC and LIMIT 1 )
The first option can also be altered to LIMIT instead of MAX

You can also insert a subquery into the field list of the main query. SELECT ... (SELECT ... FROM ...) ... FROM ... In this simple way you can probably somehow insert a message. But I never did such a thing and invent scrap.

And if you used Postgres , you could do it

 SELECT DISTINCT ON (m.id_for, m.id_from) 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, m.id_for FROM users AS fu LEFT JOIN user_message AS m ON m.id_for = fu.user_id WHERE m.id_from = "'.$_SESSION['id'].'" ORDER BY m.id DESC

It differs from your request only by adding DISTINCT ON (m.id_for, m.id_from) , which will force postgres to select only the first record for the pair (m.id_for, m.id_from), and ignore the rest.