Sort by Table Headers
Get the Code for this Tutorial
View a Working Example of This Code
Comment on this Tutorial
This tutorial will show you one way to sort table data by clicking on a heading. There are many ways to do this, virtually all of them are simple. I use this particular method because it does not reveal database column names.
First Steps
First we need a default value for the url variable that we will call 'sort'. It's value will be in the format of 'name.asc'. Notice that the actual field name is 'names'. I suggest that you use nicknames like this to avoid placing the real name of the field in your url.
<cfparam name="url.sort" default="name.asc">
We need to do a few quick checks to make sure that the url variable is properly formatted. If there is a problem with the url.sort, set it to a default value to avoid errors. What we are doing with this query is dynamically writing the 'ORDER BY' clause so that we can change it by clicking on links in the table header. So the data has to be formatted correctly to avoid errors.
<cfif (listLast(url.sort,".") NEQ "desc") AND (listLast(url.sort,".") NEQ "asc")>
<cfset url.sort = "name.asc">
</cfif>
The Query...
As usual, a very simple query is used in this tutorial. However, there is a key part of this query in the 'ORDER BY' clause, or the CFSWITCH that controls it.
<cfquery name="get_data" datasource="#dsn#">
SELECT names
FROM cfml_example_data
ORDER BY
<cfswitch expression="#listFirst(url.sort,".")#">
<cfcase value="name">names #listLast(url.sort,".")#</cfcase>
<cfcase value="age">ages #listLast(url.sort,".")#</cfcase>
</cfswitch>
</cfquery>
Building the Table
Create the html table
<table>
Now make the first row. This is the header. It will have the names of the tables header fields and the links to click to sort the data. This simple example has one field in the header, 'First Names'.
In the header, there are two links. One for sorting in descending order, one for sorting ascending order.
The links themselves are simple. Break it down into each function being used if you don't quite get it at first.
The variable 'url.sort' is used in the query above to determine what field and order to sort by. We are only using the url.sort variable to look up a condition in the cfswitch. This is handy, since we can write the url variables in a manner that does not expose the true field names to the public.
<tr>
<td>
First Names
<cfoutput>
<a href="index.cfm?sort=name.desc"><</a>
<a href="index.cfm?sort=name.asc">></a>
</cfoutput>
</td>
</tr>
Output the Data
Now that the header is in place, we can use cfoutput to loop through our data. This part is straight forward enough.
<cfoutput query="get_data">
<tr>
<td>
#names#
</td>
</tr>
</cfoutput>
And finish up the table
</table>
____________
Tutorial Code
Download the Database<cfparam name="url.sort" default="name.asc">
<!---{ validate url variable }--->
<cfif (listLast(url.sort,".") NEQ "desc") AND (listLast(url.sort,".") NEQ "asc")>
<cfset url.sort = "name.asc">
</cfif>
<!---{ here is the query }--->
<cfquery name="get_data" datasource="#dsn#">
SELECT names
FROM cfml_example_data
ORDER BY
<!---{ the cfswitch sorts the data depending on the first part of the url.sort varaible }--->
<cfswitch expression="#listFirst(url.sort,".")#">
<!---{ the ASC or DESC is the last part of the url.sort variable }--->
<cfcase value="name">names #listLast(url.sort,".")#</cfcase>
<cfcase value="age">ages #listLast(url.sort,".")#</cfcase>
</cfswitch>
</cfquery>
<!---{ Make the table to hold the data }--->
<table>
<tr>
<td>
First Names
<cfoutput>
<!---{ Make the links to click on to sort records }--->
<a href="index.cfm?sort=name.desc"><</a>
<a href="index.cfm?sort=name.asc">></a>
</cfoutput>
</td>
</tr>
<!---{ Output the data from the query }--->
<cfoutput query="get_data">
<tr>
<td>
#names#
</td>
</tr>
</cfoutput>
</table>
