Php order by date decending

[keyboard]

Hi everyone,



Use the ORDER BY in your SQL selection query. For example:



SELECT * FROM `comments` WHERE id = 1 ORDER BY date DESC


You can change DESC (descending order) to ASC (ascending order).



A while back in the thread, comments box, I asked how to make the newest comment display at the top of all the other comments. The code worked except I've just realised that if you enter another comment on the same day, then it dosn't work. Any help would be great. Here's my current code



<?php

// POST data wasnt entered, so display the comments and comment form
// view comments from database
$sql = mysql_query ("SELECT * FROM comments ORDER BY date DESC") or die(mysql_error());;
while ($row = mysql_fetch_array ($sql)) {
?>
<table border="0" cellspacing="0" cellpadding="4" width="500">
<tr>
<td>
<?php
echo $row['name'].'<br />';
?>
</td>
<td>
<?php
echo $row['date'].'<br />';
?>
</td> </tr>
</table><br />
<?php
echo $row['comments'].'<br />';

[traq]

if `date` only contains the date (like "8/29/2011", for example), then you may be out of luck. a better approach is to save a timestamp* - then your query would work "as-is." Once you retrieved the comments, you could convert the timestamp into a human-readable format using date() (http://us2.php.net/manual/en/function.date.php). for example:
print date('F jS, Y',$row['date']);
// prints something like "August 29th, 2011"
* a UNIX timestamp, which measures time in seconds since January 1 1970 00:00:00 GMT. SQL timestamps are different, and not suitable for this purpose.

[JShor]

I think I discussed in another thread recently that it's always to a MySQL user's advantage to store everything in UNIX time using the BIGINT data type. Timestamp isn't a bad way to store dates either.