[SOLVED] sort results by date

[bigrossco]

I have managed to get a table to display results from the mysql database using php but would like it to sort it by the date i.e. displays the closest date i.e. displays 28/12/2006 at the top insted of 01/12/2006

Thanks,

Ross

[taith]

that happens simply because 28>01... you'd want to put your dates in as YYYY/MM/DD which would then sort properly :)

[craygo]

If your field is actually a date field, which it should be, it will sort by itself. If you are storing the dates like you said, you shouldn't. You should store dates AS dates then format them the way you like, either with the sql statement or with php. Then the dates will sort correctly.

Ray

[obsidian]

By all means, craygo is right on... You're best off changing your data type to DATE and using it as intended; however, if your database is one in which you cannot easily change your structure, you can do something like this:
[code]
SELECT SUBSTRING(myDate, 7) AS year, SUBSTRING(myDate, 4, 2) AS month, SUBSTRING(myDate, 1, 2) AS day
FROM tableName
ORDER BY year DESC, month DESC, day DESC;
[/code]

Basically, you're going to have to split your date apart and sort by year, month and then day. This can be extremely resource intensive if you have a ton of records since you're actually doing a [b]lot[/b] of string manipulation with each and every record. You'll be much better off in the long run changing your data types, but this may help you as well.

Good luck.

[bigrossco]

I store it correctly in the database, I have made the coding for the result to show in php the correct way i.e. dd/mm/yyyy

[ted_chou12]

I would suggest you use micro time, and use sort() for accending that is the earliest goes top and read to the bottom,the last one will be the latest. OR rsort() which is reverse sort, ie. latest goes top earilest goes to bottom, BOTH OF THEM NEEDS TO PUT TIME INTO AN ARRAY BEFORE THE SORT.
By micro time, even if two users submit at nearly the same time, it will be able to diffrentiate...
Ted, hope this advice is useful.

[obsidian]

[quote author=bigrossco link=topic=120124.msg492553#msg492553 date=1167315162]
I store it correctly in the database, I have made the coding for the result to show in php the correct way i.e. dd/mm/yyyy
[/quote]
Well, if it's stored as a DATE field, all you have to do is "ORDER BY myDateField DESC" to get them to sort by the most recent date first.