I'm inserting data into a database with the following code:
<cfset minute_date = createdate(#form.year#,#form.month#,#form.day#)>
<cfquery name="addminutes" datasource="mymhsaa">
INSERT INTO `minutes` (`filename`, `date`)
VALUES ('#form.filename#', #minute_date#)
</cfquery>
|
The form that it pulls from has a separate field for the month, date and year, so when the data is entered you get something that looks like this:
filename date
February_11_2007_Minutes.pdf 2/11/2007
January_10_2007_Minutes.pdf 1/10/2007
January_21_2007_Minutes.pdf 1/21/2007
March_17_2007_Minutes.pdf 3/17/2007
April_22_2007_Minutes.pdf 4/22/2007
may_20_2007_minutes.pdf 5/20/2007
july_15_2007_minutes.pdf 7/15/2007
August_19_2007_Minutes.pdf 8/19/2007
Oct_21_2007_Minutes.pdf 10/21/2007 |
When displaying it on a page, I use:
<cfquery name="qminutes" datasource="mymhsaa">
select * from minutes
ORDER BY date DESC
</cfquery> |
and then output it like this:
<CFLOOP QUERY="qminutes" StartRow="1" ENDROW="5"> <cfoutput><tr><td height="17"> </td> <cfset date_title = DateFormat(#qminutes.date#, "long")>
<td height="17">
<a href="/minutes/#qminutes.filename#" target="_blank" class="smalltext">
#date_title#</a></td>
</tr>
</cfoutput>
</cfloop> |
The only problem is when it outputs it's not doing so in the proper order - it's looking at the DATE field like a series of numbers, not as an actual date, so in the above code even though it's supposed to output the most recent 5 records it doesn't show the October record - since 10 comes after 1 if you're doing binary sorting instead of actual date sorting. The records actually displayed with the above code are:
August 19, 2007
July 15, 2007
May 20, 2007
April 22, 2007
March 17, 2007
So, how can I get it to do an actual date sort?
