CODEWITHSUNDEEP
phpsqlmysql
Lea
Lea

Reputation: 934

Mysql query won't ORDER BY date

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

Upvotes: 2

Views: 433

Answers (2)

Mike
Mike

Reputation: 5036

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.

Upvotes: 2

Jenni
Jenni

Reputation: 1706

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')

Upvotes: 0

Related Questions