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!

About This Tutorial
Author: Mark Aplet
Skill Level: Beginner 
 
 
 
Platforms Tested: CF5,CFMX
Total Views: 115,464
Submission Date: August 03, 2003
Last Update Date: June 05, 2009
All Tutorials By This Autor: 8
Discuss This Tutorial
  • What about if you have search parameters from another page? Like a form that users use to search for data? How can the sorting be maintained without having to re-input form variables. I get an errors when using form.search variables when sorting. element form.seach is undefined. (this happens only when I click the sort button)

  • Justin, it's doing that cause your fields are not set as numbers but as text fields. If your oing to use text fields the charc length needs to be the same for all numbers. So you have to have 028 and 130 and now 028 will come before 130. Hope I made that clear.

  • First off, excellent tutorial, works great. Now, some of the fields in my datebase contain numbers, and when I sort them, it looks at the first number instead of the whole number (i.e. 130 lists before 28) any suggestions on how to fix this.

  • Noice....works perfect, easy to use...good stuff

  • If i was submitting form variables to this page, how would i maintain the sort variable and the form variables from the previous page?

  • Very easy to implement. Thank You.

Advertisement

Sponsored By...
Deep Tissue Massage and Swedish Massage Services just $39 for a 50 minute massage!