I’m building a web based chat application. Which involves users sign up and login.
Each signed up user has a unique_id Which is used to identify different users across all pages in the app.
I have these tables in MySQL database.users, messages, pinned_user.
In the the messages table I have msg_id(int),sent_by_id(using the users unique_id), sent_to_id(using the users Unique_id), msg_time(current_timestamp), message (the sent or received message).
In the pinned_user table
I have pin_id, pinned(using the users unique_id), pinned_by(using users unique_id), action(determines if a user is pinned or unpinned).
You can only chat with someone if the both of you pinned each other.
I want to order the pinned_users according to newest sent or received messages for different users in each session.
Here is my code.
$pinned_users = mysqli_query($conn, "SELECT * FROM pinned_user LEFT JOIN users ON users.unique_id = pinned_user.pinned LEFT JOIN messages ON messages.sent_by_id = pinned_user.pinned AND messages.sent_to_id = pinned_user.pinned_by WHERE pinned_user.pinned_by = $_SESSION["unique_id"] AND action = 'unpin' ORDER BY messages.msg_time DESC ");
My problem is that the pinned_users are outputted according to the number of times their sent_by_id appears in the messages table.
Any solution to this?.