Order by date ( display newest item )

[scottrohe]

Ok well I have everything right I just need to know WHICH date format I should use when entering the date into the db.
ex; I've tried:
[code=php:0]
<?php
      $date = date('dmy', strtotime($date));
?>
[/code]
so I get the result of '050806' but when I order that in a sql queue  'ORDER BY date DESC', and i test it by changing the date around on tables, it just doesn't work flawlessly. I was also wanting to add in like a timestamp so if 2 items are added at once, but one is added a second or two later, it's newer and therefore displays ahead of the one added 2 seconds before it.. what would my 'date' variable be? Thanks.

[Barand]

Best is 2006-08-06 (Y-m-d) so it is sortable

If you make it a DATETIME type and store the value NOW() into it when you add a record then you will get Y-m-d H:i:s

[nethnet]

Insert them with the MySQL function now().  That inputs a timestamp.  You can always format it when you retrieve it from the database.

[scottrohe]

thanks  :)

[onlyican]

Mysql actually stores dates in the format of Y-m-d H:i:s
Which in translation
2006-08-06 01:45:12