How to sort table ascending and descending by click state? MYSQL/ColdFusion

Question

I have a table where I can click the headers to sort the whole table by different columns. I would like to incorporate some boolean state so that I can click once to sort the table by that column ascending then click again to have it descending.

Here is my code:

 <th><A HREF="thispage.cfm?sorter=creationDate">Creation Date</A> </th>

 <cfif IsDefined("URL.sorter")>

 <cfquery datasource="Pad"  name="One">
 select * from meeting
 ORDER BY #URL.sorter# DESC
 </cfquery>

<cfelse>
 <cfquery datasource="Pad" name="One">
select * from meeting
 ORDER BY contactName
 </cfquery>
</cfif>

I believe it could be implemented by creating a boolean variable using cfset and then altering that variable state on clicking the table header and assigning one variable state to ASC and another to DESC in the first query. I'm just not sure how to implement it.

I think you would be better off doing this with javascript or jquery. In fact, look at cfgrid. It might already have this feature. I don't know for sure because I never use it. — Dan Bracuk, Feb 15 '13 at 10:55
`ORDER BY #URL.sorter#` That is a sql injection risk. Always validate any user supplied input before using it directly in a sql query. — Leigh, Feb 15 '13 at 14:36

score 2 · Accepted Answer · edited May 23 '17 at 12:27

If you wanted, you can do it pretty nicely with jQuery plugins / javascript. See: Choosing a jQuery datagrid plugin?

But to do it the old fashioned way, you add the direction to your url in your <th>. If you're only dealing with one header you can simply do something like:

<cfif structKeyExists(url, "sortOrder") and url.sortOrder eq "ASC">
    <url.sortOrder = "DESC"> 
<cfelse>
    <url.sortOrder = "ASC"> 
</cfif> 
<cfoutput>
    <A HREF="thispage.cfm?sorter=creationDate&sortOrder=#url.sortOrder#">Creation Date</A>
</cfoutput>

If you're working with multiple headers, you need to check for the direction of the current column too.

<cfif (structKeyExists(url,"sorter") and url.sorter eq "{thisColumn}")>
  and (structKeyExists(url, "sortOrder") and url.sortOrder eq "ASC")>
    <url.sortOrder = "DESC">
<cfelse>
    <url.sortOrder = "ASC">
</cfif>

You should probably put that into a function passing in the value of the column you're checking so you don't have the same thing over and over before each <th>, DRY (Don't repeat yourself).

<A HREF="thispage.cfm?sorter=creationDate&sortOrder=#sortMe({thisColumn})#">{thisColumn}</A>

Lastly, add the direction to your query

<cfquery datasource="Pad"  name="One">
    select 
        columnName1
        , columnName2
        , columnName3
    from 
        meeting
    ORDER BY 
        #URL.sorter# <cfif structKeyExists(url,"sortOrder")>#url.sortOrder#</cfif>
 </cfquery>

How to sort table ascending and descending by click state? MYSQL/ColdFusion

1 Answers1