CODEWITHSUNDEEP
phpmysql
Sam H.
Sam H.

Reputation: 338

PHP Chat System MySQL Database Order by datesent sql

I have a php chat system that I am working on. I have an issue where the query statement to grab the message is selecting them, grouping them by the two users that it was sent to and from, and then outputs the html.

Right now it only is outputting from the beginning of the first chat id to the last id when a user goes to select a chat.

I would like to sort it by the most recent chat on top because as of the moment, it is going from top to bottom from the first user that has sent a message to the last user that has sent a message. For example, if "user 1" hasn't sent a message for a while, that user will always be the first one to be outputted by the query statement, even though "user 6" has sent 8 new chats, "user 6" will still be the last one on the list to select from, this should be the opposite and the newest chat group should appear on top. I just don't know how to do that with my SQL query.

My current query statement is:

$queryall = "SELECT DISTINCT mfrom FROM chat WHERE mto='user2'";

I know it's coded wrong for what I want, and i've tried a lot of different things, can't seem to get it to do what I want. I've tried order by datesent and grouping them, but couldn't get anything to work. I believe DISTINCT is not allowing me to do it based of syntax things I am not familiar with.

mysql database row

User2 chat view

Upvotes: 2

Views: 639

Answers (1)

Patrick Moore
Patrick Moore

Reputation: 13354

You just need a simple ORDER BY:

$queryall = "SELECT DISTINCT mfrom FROM chat WHERE mto='user2' ORDER BY datesent DESC";

This orders your result set by column datesent in DESC (descending) order (newest to oldest when used on date/datetime column).

When your users have multiple messages, you may want to use GROUP BY instead of DISTINCT

$queryall = "SELECT mfrom FROM chat WHERE mto='user2' GROUP BY mfrom ORDER BY MAX(datesent) DESC";

Upvotes: 2

Related Questions