Mysql query won't ORDER BY date

Question

Hi all,

I am stuck with the following query, which won't order by it's date. Any help, or insight into what I am doing wrong will be much appreciated. The query is supposed to get an entry by thread_id, and then show the newest post in the thread, much like with a forum post, which it does fine. But when I try to order the results from newest to oldest using ORDER BY clause, it seems to ignore it.

$query = "SELECT * 
          FROM messages 
            WHERE (thread_id, received) 
             IN (SELECT thread_id, MAX(received) 
                 FROM messages 
                 WHERE receiver='$user' OR sender='$user' 
                   AND is_hidden_receiver!='1' 
                 GROUP BY thread_id) 
           ORDER BY received DESC";

Cheers, Lea

Answer 1

Is the problem that it really isn't sorting by "received", or are you just getting different results than you expect? It could be order of operations on the where clause--I'm not sure if AND or OR takes precedence. Maybe try changing this:

receiver='$user' OR sender='$user' AND is_hidden_receiver!='1'

to whichever one of these you are wanting:

(receiver='$user' OR sender='$user') AND is_hidden_receiver!='1'

receiver='$user' OR (sender='$user' AND is_hidden_receiver!='1')

Answer 2

You were using the PHP time() function to generate a value to be inserted into an INT(11) column. I'm a little mystified as to why this was sorting incorrectly. I will update this answer if I figure out how to explain it concisely.

This feature is built into MySQL, it is the TIMESTAMP column type. You should probably read up on it a bit more before being happy with this solution. It has some interesting properties, depending on how to define your table, a column of type TIMESTAMP can act either as a creation timestamp or a modification timestamp.