I am trying to extract from the database the last message of each conversation just like Facebook does and the other instant messaging platforms.
Code that I am using to extract the messages
<?php
// Sacar los usuarios con los que he tenido una conversacion
$stetament = $conexion->prepare("
SELECT users.id_user, users.name, users.surname,
users.photo_profile, messege.messege, messege.create_at_messege
FROM users INNER JOIN messege
WHERE messege.id_emisor = $id OR messege.id_receptor = $id
");
$stetament->execute(array());
$result = $stetament->fetchAll();
Which is my goal ?
My goal is like the screenshot above of my conversations on Facebook. I want it to show me a list of the people with whom I have had conversations and show me the last message we have exchanged, the date and etc... as I show in my screenshot
Take the same code and execute it in SQL from phpmyadmin and it returns this result that may be useful for a little more information.
That query is a bit more complicated than it sounds. Perhaps the most difficult thing is to locate the interlocutor. Then it's just grouping, combining and ordering. Although it admits several equivalent solutions, I propose something like this for it
id_user=2
:For this dataset:
You will get this result:
I hope it solves your problem. Leave me any questions in the comments.