Accending and Descending SQL order problem.

[xiofire]

I'm in a jam. I'm working on new profiles for my members, but I'm having a "Forum Activity" problem.

http://content.screencast.com/users/xiofire/folders/Jing/media/51511e1a-99bd-4dbc-a429-b3e5182d5835/2009-06-13_1215.png

Now, if you notice, the threads were all replied to on the same date, today.
And say there was a thread from yesterday, June 12. It would appear under the June 13 threads, but the problem is, I replied to the thread "Hi Everyone! xD" last, and the thread "I can has admin!" First, and I want it to be the other way around.

I hope I explained this well enough. Here is the code.


<table border="0" cellspacing="5" cellpadding="2">
<?php
$pro1="SELECT * FROM censored WHERE a_user_id='$profile' ORDER BY a_date DESC LIMIT 5";
$pro2=mysql_query($pro1);
while($pro=mysql_fetch_array($pro2)) {
?>
<tr><td><a href="thread.php?id=<?=$pro['question_id']?>"><?=$pro['a_topic']?></a>&nbsp;<small><em><?=$pro['a_date']?></em></small></td></tr>
<?php
}
?>
</table>

I tried to order it by id and date, nothing. I tried to change it to ASC, and it took me back to my first post.

Any help?

[forum_amnesiac]

Can you tell us what you are storing in the a_date field, with an example direct from the table.

To get the records in exactly the order they were posted you need to have both the date and the time in a format on which they can be already sorted, eg YYYYMMDDHHMMSS if a string or as the full timestamp.

[xiofire]

Do all I need is to add time to the dates? It's currently date("F j, Y"); = June 14, 2009.

[forum_amnesiac]

If you are keeping the date field as a string with the format June 14, 2009 as per your example then the sort will behave very strangely, ie August 15 2010 will come before June 14, 2009.

The sort works on each character in a string and it will obviously decide that A comes before J.

There are only really 2 good ways to get to sort properly, that is to use the Timestamp method or to store it in a string as YYYYMMDDHHMMSS. The best method is to use the timestamp.

For the second method you must use a 2 character numeric representation for the month and day, eg 01, 02, etc. If you store the dates as 2009July24 and 2009Aug15, the sort process will again put the August date before the July date.

For either storage method you can reformat the date when presenting it back.