Hello gobodyworks,
I solved your problem.
The key to displaying dates/times in chronological order is to sort by a field that represents the date and/or time as a duration since a fixed date/time.
I looked at the MySQL date format features and they contain the %-values. The %j element was very useful - it represents a date as the number of days since Jan 1 of that year. This is aka day-of-the-year ("doty").
So I created a string that chronologically orders each date/time: the year, followed by "doty", then the time in 24-hour format. This format string is short - making it easy to follow: %Y%j%T. July 1st, 2012 at 12 noon would be:
%Y %j %T
201218212:00:00
The next thing to do is to prepend this string to the real date/time string.
If I use the additional format string of "%W, %M %D, %Y at %r" So you're left with:
[201218212:00:00] Sunday, July 1st, 2012 at 12:00:00
Now, use the HTML tag attribute called "hidden". This keeps text present, but not visible.
An example is: <span hidden="hidden">Text you won't see.</span>
<span hidden="hidden">[201218212:00:00]</span>Sunday, July 1st, 2012 at 12:00:00
When you click sort, it will sort by the data at the beginning of the field until a difference is found. Well, the differences are contained in the text inside the [ and ]. But that text isn't displayed, because you likely don't wish to show it. lol
I made an example page on my website:
www.halscrib.com/index.php/hidden/date-test
The returned data is every hour of every day from June 1, 2012 to Dec 31, 2012. You can see how the data is all properly sorted. I also included a second column, which is the sort key itself - the text inside the <span hidden="hidden"> </span> tags. This allows you to see what the table is sorting on.
The full SQL command is:
| Code: |
SELECT
CONCAT('<span hidden="hidden">[', DATE_FORMAT(DateTime, '%Y%j%T'), '] </span>', DATE_FORMAT(DateTime, '%W, %M %D, %Y at %r')) AS Tournament,
CONCAT('[', DATE_FORMAT(DateTime, '%Y%j%T'), ']') AS SortKey
FROM Tournaments;
|
Hopefully that works for you too.
