John Davidson

Message:

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:

this is my code:

<?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>";

                }

            ?>

---

My Answer:
To sort users based on the latest chats, you can use a query like the following:

php
SELECT users.id, users.name, MAX(chats.created_at) AS last_chat
FROM users
LEFT JOIN chats ON users.id = chats.user_id
GROUP BY users.id
ORDER BY last_chat DESC


This query will select the user's id, name, and the latest chat timestamp for each user. It will then group the results by user id and order them by the latest chat timestamp in descending order.

Make sure to replace `users`, `chats`, `id`, `name`, `created_at`, and `user_id` with the actual table and column names in your database.

If you are still facing issues with repeating user names for every message, you may need to adjust your query or the way you are fetching and displaying the data in your code. Make sure you are only fetching the user's name once and not repeating it for each chat message.