-1

I was trying to build a chat plugin in my site but I got problem while sorting the incoming messages in the Inbox. When I use the code below, it doesn't order the messages according to new sent but orders according to history of sent messages:

E.g. if I send message to "A" at first, "B" after that and "C" at last, the code I works fine up to here. It shows C at Top, B at middle and A at end. But When I again send message to B, the "B" doesn't come up at the top.

Edit: Someone tagged this question as duplicate. The problem about that question is abit different. I already did extract the unique row (as asked by that question) but I cannot sort it according to time.

Here is the code I use and please ignore the mysql_* tag I used here. It is just for testing.

<?php

$sql = mysql_query("SELECT DISTINCT `from` FROM `message` WHERE `to`='$username' ORDER BY `time` DESC");

// using loop

while($row = mysql_fetch_assoc($sql)){
echo $row['from'];
echo "<br/>";
}

?>
vhu
  • 12,244
  • 11
  • 38
  • 48
Aadarsha
  • 75
  • 8
  • 1
    Possible duplicate of [How to use DISTINCT and ORDER BY in same SELECT statement?](http://stackoverflow.com/questions/5391564/how-to-use-distinct-and-order-by-in-same-select-statement) – Jeremy Harris Oct 26 '15 at 13:02
  • 1
    Possible duplicate of [Using 'distinct' in a MySQL query](http://stackoverflow.com/questions/502094/using-distinct-in-a-mysql-query) – omid Oct 26 '15 at 13:26

3 Answers3

1

You are ordering by a not selected column

if you want order by time i think you should select this column : try 

  $sql = mysql_query("SELECT `from` , max(`time`) FROM `message` 
     WHERE `to`='$username'  GROUP BY  `from` ORDER BY `time` DESC " );

Grouping by not require distinct

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • since you are grouping by from, I suspect that the DISTINCT clause is not required – ESG Oct 26 '15 at 13:47
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY `from`' at line 2 – Aadarsha Oct 26 '15 at 14:35
1

For messages you can select rows ordered by id desc like this:

SELECT * FROM `message` WHERE `to`='username' ORDER BY id DESC

Sender list you can get like this:

SELECT MAX(`id`),`from` FROM `message` WHERE `to`='username' GROUP BY `from`  ORDER BY MAX(`id`) DESC
nagiyevel
  • 397
  • 3
  • 16
0

Do not order messages by time. The best practice is to having unique autoincrement id column in message table and ordering messages by they's ids

nagiyevel
  • 397
  • 3
  • 16