Order by date DESC doesn't work.

[V]

I have a query that counts the number of items that belong to a category and echoes the date of the most recent item in that category. All works fine except for the date. For some reason it isn't querying the most recent. The code is..

 

DB Connection
.....
query all categories {
.....
$sql = "SELECT *, COUNT(post_id) as total_posts FROM posts WHERE cat_id = {$row["cat_id"]} ORDER BY post_date DESC";				
$posts_result = $connection->query($sql) or die(mysqli_error($connection));

while ($row = $posts_result->fetch_assoc()) {	

                        $total_posts = $row['total_posts'];
		$post_date = $row['post_date'];


                       echo $total_posts;
                       echo $post_date;

        }//end posts loop
}//end category loop

 

I'm not sure what I'm doing wrong. I have ORDER BY post_date DESC which I think should retrieve the most recent date.. :-\ Any ideas?

[bh]

Hi,

 

Try this:

ORDER BY 'post_date' DESC"

[V]

thanks for the suggestion bh. The single quotes don't change anything. What are they indented to do? I use them in queries just for variables.

[Pikachu2000]

What is the data type of the post_date field?

[V]

Pikachu, sorry I forgot to add that. post_date is TIMESTAMP and echoes for example, 2010-07-17 12:48:03

 

[Pikachu2000]

That's odd. It should work. Can you paste the query string into phpMyAdmin (if you use it) and see what happens?

[V]

Thanks for the suggestion. I tried SELECT * FROM `posts` ORDER BY post_date ASC

 

and it lists the newest on top correctly like this

 

2010-07-17 13:28:12

 

2010-07-17 12:48:36

 

2010-07-17 12:48:03