php - how to sort users based on the latest chats

i have a chat website and i want the user to have a list available of users sorted by who they last chatted with(like whatsapp). how do i do this? i tried many stack overflow answers but none of them worked for me so far. when using the code i use now the names of the users repeat for every message that exists. this query isn't working: "SELECT * FROM dms WHERE sentTo = ".$_SESSION['id']." or sentBy = ".$_SESSION['id'].";" this is what my database looks like:

<?php
                $sql = "SELECT * FROM dms WHERE sentTo = ".$_SESSION['id']." or sentBy = ".$_SESSION['id'].";";
                $result = mysqli_query($conn, $sql);
                if (mysqli_num_rows($result) > 0) {
                    while ($row = mysqli_fetch_assoc($result)) {
                        $sql2 = "SELECT id, username FROM users WHERE id = ".$row['sentTo'].";";
                        $result2 = mysqli_query($conn, $sql2);
                        if (mysqli_num_rows($result2) > 0) {
                            while ($row2 = mysqli_fetch_assoc($result2)) {
                                echo "<a href='dms.php?talkingTo=".$row2['id']."'>".$row2['username']."</a>";
                            }
                        }else{
                            echo "<p>It's empty</p>";
                        }
                    }
                }else{
                    echo "<p>It's empty</p>";
                }
            ?>

"SELECT * FROM dms WHERE sentTo = ".$_SESSION['id']." or sentBy = ".$_SESSION['id'].";

From an accepted answer to a mostly similar question I got "one method of doing what you want that uses a correlated subquery, to find the minimum created date/time for a matching conversation".

It also sorts the results based on the date.

So making these corrections and customizing the query from the older question to your context - the above query you used should be changed to(Replace where 1 by concatenating the id from session: $_SESSION['id']):

SELECT m.*
FROM dms m
WHERE 1 in (sentBy, sentTo) AND
      m.date = (SELECT MAX(m2.date)
                   FROM dms m2
                   WHERE (m2.sentBy = m.sentBy AND m2.sentTo = m.sentTo) OR
                         (m2.sentBy = m.sentTo AND m2.sentTo = m.sentBy) 
                  )
ORDER BY m.date DESC

I run this query on an identical dataset of your dms table and got 2 unique results.

One additional thing you may need to check is the link you want to generate by using the anchor:

echo "<a href='dms.php?talkingTo=".$row2['id']."'>".$row2['username']."</a>";

This is echoing out the id of the current user. I think you were supposed to echo out the id of the who they are chatting to? If so you need to run the query to get who it is sent from instead.

Hope this helps.

Solution:

We need to group the dms records based on who the chat is with, knowing that I (id 1) am either the sender or recipient.

If it is sentTo 1 (Me) and sentBy 2 (You) then it is sentBy we are interested in. Conversely, if it is sentTo 2 (You) and sentBy 1 (Me) then it is sentTo we are interested in.

Putting this into SQL we have -

IF(sentTo = 1, sentBy, sentTo)

We can now use this in our query of the dms table to group by who we are interacting with and use MAX to find the most recent interaction -

SELECT IF(sentTo = 1, sentBy, sentTo) chatWith, MAX(date) mostRecent
FROM dms
WHERE sentTo = 1 OR sentBy = 1
GROUP BY chatWith

In your code you are running an additional query per row returned by the first query. This is not necessary as we can join to the users table -

SELECT u.id, u.username, t.mostRecent
FROM (
    SELECT IF(sentTo = 1, sentBy, sentTo) chatWith, MAX(date) mostRecent
    FROM dms
    WHERE sentTo = 1 OR sentBy = 1
    GROUP BY chatWith
) t
JOIN users u ON t.chatWith = u.id
ORDER BY t.mostRecent DESC

Putting all of this into your code we end up with -

<?php

$sql = "
    SELECT u.id, u.username, t.mostRecent
    FROM (
        SELECT IF(sentTo = {$_SESSION['id']}, sentBy, sentTo) chatWith, MAX(date) mostRecent
        FROM dms
        WHERE sentTo = {$_SESSION['id']} OR sentBy = {$_SESSION['id']}
        GROUP BY chatWith
    ) t
    JOIN users u ON t.chatWith = u.id
    ORDER BY t.mostRecent DESC";

$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "<a href='dms.php?talkingTo={$row['id']}'>{$row['username']}</a><br>";
    }
} else {
    echo "<p>It's empty</p>";
}

I have tested this with 1M randomly (ish) generated rows and it returns consistently in less than 0.02s on my local dev machine.

UPDATE - some test results

EXPLAIN output for each of the three queries with a test dataset of 10k rows - users.id between 1 and 500 and date between 2021-01-01 and current.

/* groupwise max */
EXPLAIN
SELECT u.id, u.username, t.mostRecent
FROM (
    SELECT IF(sentTo = 1, sentBy, sentTo) chatWith, MAX(date) mostRecent
    FROM dms
    WHERE sentTo = 1 OR sentBy = 1
    GROUP BY chatWith
) t
JOIN users u ON t.chatWith = u.id
ORDER BY t.mostRecent DESC;

# Serverside execution time: 0.001s
# Rows examined: 66
# Rows returned: 22