Dynamic Sorting

I find that I frequently need to sort and view records in ascending and descending order for several fields. This was at first very complex and cumbersome, but with the help of cfswitch you can sort as many fields as you want in any order you want. I'll explain below.

Lets say you are Pablo Varando and have 489 DVD's in your collection. Lets also say you actually wanted to list all the specs about the movies so that friends and family can find particular movies quickly.

For the sake of time I'll use my movie collection as an example. I only own a few movies so it makes it easier to create a sample database.

In the example below I have only 6 movies. As pathetic as my movie collection is, the ability to sort any given field would be very useful if I had Pablo's movie collection.

If you click any linked header title in the example below it will change the dynamic sorting order on the fly. As you can see this can be quite useful if you wanted to see all the movies made in 1999 or by Warner Bros.

How it Works

Lets start with the query. This is a generic query with an ORDERED BY statement in it. Typically you would just specify what field you wanted to order and whether it was in ascending or descending order.

Since this is a dynamic sort we need to create a cfqueryparam and set it 1. In this case it will order the query first by MovieTitle, then ProductionCompany.

Each possible ordering value is set in a cfcase with the values being indicate therein. As you can see, the values are grouped in pairs, (1 & 2 order the same fields just in different orders.) That way if you click the header link once it will order  in descending order. if you click it again it will be the opposite of that (ascending) In the example below I ordered each field so I can toggle it in the page.

<cfparam name="sort" default="1">

<cfquery name="qryMovies" datasource="MovieList">
      SELECT *
      FROM MovieList
      ORDER BY
<cfswitch expression="#sort#">

<cfcase value="1">MovieTitle, ProductionCompany</cfcase>
<cfcase value="2">MovieTitle desc, ProductionCompany</cfcase>
<cfcase value="3">ProductionCompany</cfcase>
<cfcase value="4">ProductionCompany desc</cfcase>
<cfcase value="5">MovieYear, MovieTitle, ProductionCompany</cfcase>
<cfcase value="6">MovieYear desc, MovieTitle, ProductionCompany</cfcase>
<cfcase value="7">Catagory, MovieTitle, ProductionCompany</cfcase>
<cfcase value="8">Catagory desc, MovieTitle, ProductionCompany</cfcase>

</cfswitch>
</cfquery>

Next comes the html output. Nothing too fancy here. About the only thing we need to look at is the header row. This row contains the links to change the sort order for the given fields. The main thing here is the IIF statement. It checks to see what the value is currently set to, and makes the link for the opposite sort order. For example, lets say the default sort was used to generate the page. (1) Since you would want the next click of the link to change the sort order, the link has to change to a sort value of 2. That's all there is to it. The values are checked for each header, and changed accordingly.

<table border="1" cellpadding="4" cellspacing="0" width="100%">

<tr>
<td colspan="4"><!--- Title Row --->
<h3>Movie Inventory</h3>
</td>
</tr>

<cfoutput>
<tr bgcolor="##E5E5E5"><!--- Header Row --->
<td><a href="movies.cfm?sort=#IIF(sort is 1, '2', '1')#">Movie Title</a></td>
<td><a href="movies.cfm?sort=#IIF(sort is 3, '4', '3')#">Production Company</a></td>
<td><a href="movies.cfm?sort=#IIF(sort is 5, '6', '5')#">Movie Year</a></td>
<td><a href="movies.cfm?sort=#IIF(sort is 7, '8', '7')#">Category</a></td>
</tr>
</cfoutput>

<cfoutput query="qryMovies"><!--- Data Rows --->
<tr>
<td>#qryMovies.MovieTitle#</td>
<td>#qryMovies.ProductionCompany#</td>
<td>#qryMovies.MovieYear#</td>
<td>#qryMovies.Catagory#</td>
</tr>
</cfoutput>

</table>

That's it you done! To view a working example go to http://www.pixeljunkie.org/easycfm/index.cfm?inc=mov

Download all associated files of this tutorial!