Quick example of table sorting with ColdFusion and jQuery

This post is more than 2 years old.

For a while now I've been meaning to take a look at table sorting via jQuery. I finally got a chance to play with it last night and write up a quick demo. My example makes use of Tablesorter, a jQuery plugin that adds (wait for it) table sorting to existing tables. Check the plugin's web site for full documentation on options and demos. Here is a quick example of how easy this is to use.

I began with a simple ColdFusion query and table display:

<cfquery name="getArt" datasource="cfartgallery"> select artname, description, price, issold from art </cfquery>

<table border="1"> <tr> <th>Art</th> <th>Description</th> <th>Price</th> <th>Sold</th> </tr> <cfoutput query="getArt"> <tr> <td>#artname#</td> <td>#description#</td> <td>#dollarFormat(price)#</td> <td><cfif issold>Yes<cfelse>No</cfif></td> </tr> </cfoutput> </table>

There isn't anything special here. I grab art records from the ColdFusion cfartgallery demo database and display the name, description, price, and sold properties. Now let's look at how easy it is to add in the sort.

First - you need to separate the header of the table from the rest of the data. This is done with the TBODY and THEAD tags.

<table border="1" id="mydata"> <thead> <tr> <th>Art</th> <th>Description</th> <th>Price</th> <th>Sold</th> </tr> </thead> <tbody> <cfoutput query="getArt"> <tr> <td>#artname#</td> <td>#description#</td> <td>#dollarFormat(price)#</td> <td><cfif isBoolean(issold) and issold>Yes<cfelse>No</cfif></td> </tr> </cfoutput> </tbody> </table>

Also note I added an ID to the table. As you know, most jQuery operations will require you to name the items you want to manipulate. Ok, so next up I add in the JavaScript libraries:

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script> <script type="text/javascript" src="/jquery/jquery.tablesorter.min.js"></script>

Next - I enable the sorting:

<script> $(document).ready(function() { $("#mydata").tablesorter(); }); </script>

And um - that's it! Considering that I normally have jQuery included already, I only needed to a) add tbody, theader, and an ID, b) include one more library, and c) write one line of JavaScript. Sweet.

As I said, you should check the web site for more details. One simple change you may want to make is an initial sort. That's as easy as:

$("#mydata").tablesorter({sortList:[[0,0]]})

You can add CSS to add pretty arrows to the headers, or keep it simple, as I did:

<style> th { cursor: pointer; } </style>

You can see a full demo of this here.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate for HERE Technologies. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Jim Papaleo posted on 1/28/2010 at 6:32 PM

Ray,
Nice to know. But, what if the data spans more than one view of the table? (ie. 100 rows showing 25 at a time). Will it sort all the data or just the visible data?

Comment 2 by Raymond Camden posted on 1/28/2010 at 6:33 PM

This is just for visible data. I plan on looking at jqGrid in the future for pagination. I first used jqGrid working with Scott Stroz, but haven't had a time to work up a nice blog entry on it.

Comment 3 by Brian Swartzfager posted on 1/28/2010 at 6:57 PM

@Jim In addition to the jqGrid Ray mentioned, another table sorting plugin that will sort even paginated tables is dataTables (http://datatables.net). dataTables also lets you filter the records (though only with a single filter input).

Comment 4 by Gary Stanton posted on 1/28/2010 at 7:02 PM

There's a seperate 'sub' plugin for TableSorter called 'Pager' which handles pagination nicely.
I've been using it for a while. Of course you have to load the whole recordset before the JS kicks in and paginates it, but it's not too slow unless you're also loading images for each record.
Perhaps there's a way to pull each page's content in using AJAX?

Comment 5 by Jim Papaleo posted on 1/28/2010 at 7:03 PM

I use jqGrid (v 3.5) on a portal application. A CF'er named Kumar has some good examples out there. http://www.coldfusion-ria.c...

Comment 6 by Dan G. Switzer, II posted on 1/28/2010 at 8:20 PM

@Raymond:

I noticed you're using the URL:
http://ajax.googleapis.com/...

I actually don't recommend using this URL since it always loads the latest version of jQuery. While this may seem ideal, there are often compatibility issues that can crop up between revisions (especially changes like from 1.3.x to 1.4.x) and all the sudden code that was working yeterday, breaks for no apparent reason.

It's best to specify a specific revision, that way you can test your application/code with the newest revision before automatically updating.

Comment 7 by Phillip Senn posted on 1/28/2010 at 8:37 PM

The neat thing about jqGrid is that it works with jQueryUI themeroller. You can pick from a bunch of different skins that are all hosted at Google.
Here's a link to my example, which contains a zip file you can download:
http://www.cfmzengarden.com...

Comment 8 by Dan Fredericks posted on 1/28/2010 at 8:38 PM

I incorporated this at my new job and they really liked how it worked. the table sort website give you good examples of how to enhance the table sort via ajax.

Comment 9 by Brian Lang posted on 1/28/2010 at 9:33 PM

I am also using the DataTables plugin (www.datatables.net). On a recent project I used it along with the JQuery UI. DataTables supports the JQuery UI. Very sweet. And it's being used at the Olympics next month.

Comment 10 by Raymond Camden posted on 1/28/2010 at 9:44 PM

@DanS: I live on the edge. ;) I'm not sure I agree with you, but at the same time, it certainly doesn't hurt to use a specific version.

Comment 11 by Michael De Jonghe posted on 1/29/2010 at 12:37 AM

I've just started using TableSorter in the last few months. I'm not yet a CFB convert... So I "fixed" the configuration files in DW to add the <thead><th> and <tbody> tags to my favorite DW Dynamic Table command.

You'll want to save a copy of this file:

C:\Program Files\Adobe\Adobe Dreamweaver CS4\configuration\Commands\ServerObjects\ColdFusion\DynamicTable_main.edml

Then copy over this bit of code:

<participant version="5.0">
<insertText location="afterSelection">
<![CDATA[
<table<@if (@@Border@@) @> border="@@Border@@"<@endif@><@if (@@CellPadding@@) @> cellpadding="@@CellPadding@@"<@endif@><@if (@@CellSpacing@@) @> cellspacing="@@CellSpacing@@"<@endif@>>
<thead>
<tr>
<@ loop (@@FieldLabelArray@@) @>
<th>@@FieldLabelArray@@</th>
<@ endloop @>
</tr>
</thead>
<tbody>
<cfoutput query="@@RecordsetName@@"<@ if (@@PageSize@@) @> startRow="#StartRow_@@RecordsetName@@#" maxRows="#MaxRows_@@RecordsetName@@#"<@endif@>>
<tr>
<@ loop (@@FieldNameArray@@) @>
<td>#@@RecordsetName@@.@@FieldNameArray@@#</td>
<@ endloop @>
</tr>
</cfoutput>
</tbody>
</table>
]]></insertText>
</participant>

Comment 12 by Dan G. Switzer, II posted on 1/29/2010 at 2:41 AM

@Raymond:

Trust me, I've used jQuery since v1.1 and you really don't want to assume your code is always going to work with the latest and greatest code. In every point revision there have been another major changes that you absolutely need to test existing code before implementing in production.

While you might not run into an issue, as your usage of jQuery expands, then the more likely you'll run into compatibility issues. We utilize jQuery heavily in my day job, and I've had to make changes to code everytime jQuery has been updated to a new revision in order to get the code working 100%.

The worst thing that can happen to you is for an application just to stop working with no clue to why it's not working. By relying on a 3rd party to provide your application with code that at any time could change, it makes it very hard to find changes in behavior.

Image knowing that an app that's been working fine for years, all the sudden broke. You knew the code hasn't changed, the server hasn't changed, etc. How long is it going to take you to finally figure out that what has changed as the version of a library being loaded into the page. My guess is that's way down in the chain of what most people are going to look at.

I'm just pointing this out because in my own experience if I had relied on automatically using the latest build of jQuery, I would have seen several applications break without reason numerous times.

Also, one thing to keep in mind is that the jQuery UI has traditionally been tied very tightly to specific versions of jQuery. Most versions of the UI library only work with specific revisions of jQuery.

Comment 13 by Raymond Camden posted on 1/29/2010 at 2:44 AM

Very good points, Dan. I've only used jQuery for a short time now so I've not had to worry much about updates.

Comment 14 by Don Blaire posted on 1/29/2010 at 2:53 AM

Thanks for the posting and the live demo example. The methods I have used in the past was not always stable in sorting a table. I am looking forward to using what you have demonstrated.

Comment 15 by Dan Parker posted on 1/29/2010 at 4:57 AM

jqGrid also has a table 2 grid option, while not as efficient as tablesorter, allows for a consistent look for paged or non-paged tables.

jqGrid w/efficient methods of SQL paging (http://blog.pengoworks.com/... and CF make a great combo.

Comment 16 by Brig posted on 7/22/2011 at 12:24 AM

Thanks for your code and demos - I love it and am using it extensively for a non-profit's internal client tracking application. Question: can sort order be saved to a session variable somehow? So if a user sorts, leaves the page and returns, his sort preference is persistent?

Comment 17 by Brian Swartzfager posted on 7/22/2011 at 12:33 AM

@Brig: It doesn't look like tableSorter has any built-in mechanism for that, but a quick Google search turned up a few links (http://stackoverflow.com/qu... and http://ybits.net/2009/10/ma... on ways to read the current sort state of the table.

And once you have the current sort state, you should be able to preserve that in a cookie, then just look for the cookie on page load and if it exists use it to set the initial sort.

Comment 18 by Brig posted on 7/22/2011 at 12:42 AM

Brian, I ran into the cookie method in my research. I was hoping to be able to do it with cf session and not cookies though, since many of my users will have cookies disabled. Alas! But thanks for your reply!

Comment 19 by Phillip Senn posted on 7/22/2011 at 1:05 AM

The bridge between JavaScript and ColdFusion is the DOM. ColdFusion can populate the DOM to tell JavaScript something, and JavaScript can populate the DOM. If you are comfortable with ajax, then you can have JavaScript send an ajax command to a .cfm page to populate the session scope. But if that's too complicated, then have js populate a form variable and when the user eventually DOES do a submit, set the session variable equal to the form variable.

Comment 20 by Chris Bowyer posted on 3/25/2014 at 7:31 AM

Nice! Will try this out. Note. Demo sorting does not appear to work. I have tried in Safari and Chrome.

Comment 21 by Raymond Camden posted on 3/25/2014 at 2:53 PM

The tablesorter JS file is 404ing. It is an old demo so I probably won't fix.

Comment 22 by Raymond Camden posted on 3/25/2014 at 2:57 PM

Ok I lie - I fixed it.

Comment 23 by Chris Bowyer posted on 3/26/2014 at 1:09 AM

Cool! I have no idea how you fit everything in each day anyway. Maybe you could do an article on that one day. I should mention, I got it up and running immediately from your instructions. The only thing that took a bit of time was figuring out how to extend it with a jQuery UI theme - mainly because of not immediately tripping over the documentation I needed. URL available on request.

Comment 24 by Raymond Camden posted on 3/26/2014 at 1:15 AM

I really haven't played much with jQuery UI lately. It works - and I know it is there - but I just don't find myself needing it often.

Comment 25 by Chris Bowyer posted on 3/26/2014 at 2:09 AM

It hasn't really changed much in years and although I would love to look at other platforms, I just don’t find the time. I would also like to blog, in particular about things like my experience yesterday, but I spend so much time writing other peoples' content, probably more on that than actually coding these days, I don’t find time for that either. A bit like a mechanic who never gets around to fixing his/her own car - which reminds me. I really should replace the rear wiper that died years ago.

Comment 26 by Chris Bowyer posted on 3/26/2014 at 3:42 AM

For extending with jQuery UI, here's the required documentation - http://mottie.github.io/tab...