Nowadays we have ways of sorting the display results from a query without resubmitting the page by using Ajax. Long time ago I wrote a custom tag with the help of my coworker Jason Sheppard to do the same by submitting the page with the new sort field information.

So, for those who do not want Ajax or do not know how to do it, I converted my custom tag into a component and I’m making it available here. Feel free to implement more features, just don’t forget to send me a copy.

You may download the code here.

To call the function you will need to pass the following required arguments:

  • datasource: your datasource
  • tableName: the data table
  • fieldNames: the columns you want to retrieve

There are other optional parameters:

  • whereClause
  • groupClause
  • sortBy
  • orderBy
  • loadURL
  • queryName
  • dbUserName
  • dbPassword
  • border
  • cellPadding
  • cellSpacing
  • thBgColor
  • tdBgColorEven
  • tdBgColorOdd
  • tdBgColor
  • rowStripes
  • thTextColor
  • tdTextColor
  • width
  • align
  • fontFamily
  • fontSize
  • thFontSize
  • isFusebox

Here is the component:
<cfcomponent displayname="sorteQueryDisplay">
<cffunction name="qDisplay" access="remote" returntype="Any" output="false">
   <cfargument name="datasource" type="string" required="true" />
   <cfargument name="tableName" type="string" required="true" />
   <cfargument name="fieldList" type="string" required="true" />
   <cfargument name="whereClause" type="string" required="false" default="" />
   <cfargument name="groupClause" type="string" required="false" default="" />
   <cfargument name="sortBy" type="string" required="false" default="" />
   <cfargument name="orderBy" type="string" required="false" default="" />
   <cfargument name="loadURL" type="string" required="false" default="#cgi.script_name#" />
   <cfargument name="queryName" type="string" required="false" default="qSorted" />
   <cfargument name="dbUserName" type="string" required="false" default="" />
   <cfargument name="dbPassword" type="string" required="false" default="" />
   <cfargument name="border" type="string" required="false" default="0" />
   <cfargument name="cellPadding" type="string" required="false" default="2" />
   <cfargument name="cellSpacing" type="string" required="false" default="1" />
   <cfargument name="thBgColor" type="string" required="false" default="gray" />
   <cfargument name="tdBgColorEven" type="string" required="false" default="ffffcc" />
   <cfargument name="tdBgColorOdd" type="string" required="false" default="f0f0f0" />
   <cfargument name="tdBgColor" type="string" required="false" default="Cornsilk" />
   <cfargument name="rowStripes" type="boolean" required="false" default="true" />
   <cfargument name="thTextColor" type="string" required="false" default="white" />
   <cfargument name="tdTextColor" type="string" required="false" default="navy" />
   <cfargument name="width" type="string" required="false" default="100%" />
   <cfargument name="align" type="string" required="false" default="left" />
   <cfargument name="fontFamily" type="string" required="false" default="Arial,Helvetica,Sans-serif" />
   <cfargument name="fontSize" type="string" required="false" default="9pt" />
   <cfargument name="thFontSize" type="string" required="false" default="11pt" />
   <cfargument name="isFusebox" type="boolean" required="false" default="false" />
   <cfquery name="#arguments.queryName#" datasource="#arguments.datasource#" dbUserName="#arguments.dbUserName#" dbPassword="#arguments.dbPassword#">
      select #arguments.fieldList#
      from #arguments.tableName#
   <cfif len(trim(arguments.whereClause))>
      where #arguments.whereClause#
   </cfif>
   <cfif len(trim(arguments.groupClause))>
      group by #arguments.groupClause#
   </cfif>
   <cfif len(trim(arguments.sortBy))>
      order by #arguments.sortBy# #arguments.orderBy#
   </cfif>
   </cfquery>
   <cfif arguments.fieldList is "*">
      <cfset arguments.fieldList = evaluate("#arguments.queryName#.columnList")>
   </cfif>
   <cfsavecontent variable="result">
      <cfoutput>
      <table border="#arguments.border#" cellpadding="#arguments.cellPadding#" cellspacing="#arguments.cellSpacing#" width="#arguments.width#">
      <tr>
      <cfloop list="#arguments.fieldList#" delimiters="," index="field">
         <th style="font-family:#arguments.fontFamily#;font-size:#arguments.thFontSize#;color:#arguments.thTextColor#;background-color:#arguments.thBgColor#">
            <a href="#arguments.loadURL#?#Querystring(cgi.QUERY_STRING,arguments.isFusebox)#&sort=#field#">#field#</a>
         </th>
      </cfloop>
      </tr>
      <cfloop query="#arguments.queryName#">
         <cfif arguments.rowStripes>
            <cfset bgcolor = "#iif(evaluate(arguments.queryName & '.currentRow') mod 2,de(arguments.tdBgColorEven),de(arguments.tdBgColorOdd))#">
         <cfelse>
            <cfset bgcolor = arguments.tdBgColor>
         </cfif>
         <tr>
      <cfloop list="#arguments.fieldList#" delimiters="," index="field">
         <td style="font-family:#arguments.fontFamily#;font-size:#arguments.fontSize#;color:#arguments.tdTextColor#;background-color:#bgcolor#">#evaluate(arguments.queryName & "." & field)#</td>
      </cfloop>
      </tr>
   </cfloop>
   </table>
   </cfoutput>
   </cfsavecontent>
   <cfreturn result />
</cffunction>
<cfscript>
function QueryString(str,isFusebox) {
   var newstr="";
   for (i=1;i LTE ListLen(str,"&"); i=IncrementValue(i)) {
      if(not isFusebox){
         if (NOT ReFindNoCase("sort=", ListGetAt(str,i,"&")) AND NOT ReFindNoCase("ord=", ListGetAt(str,i,"&")) AND NOT ReFindNoCase("fuseaction=", ListGetAt(str,i,"&"))) {
            newstr=ListAppend(newstr,ListGetAt(str,i,"&"),"&");
      }
   }
   else {
      if (NOT ReFindNoCase("sort=", ListGetAt(str,i,"&")) AND NOT ReFindNoCase("ord=", ListGetAt(str,i,"&"))) {
         newstr=ListAppend(newstr,ListGetAt(str,i,"&"),"&");
         }
      }
   }
   return newstr;
}
</cfscript>
</cfcomponent>

Here is how you call the component:
<cfparam name="url.sort" default="" />
<cfparam name="url.ord" default="" />
<cfscript>
com = createObject("component","sortedQueryDisplay");
result = com.qDisplay(
tableName = "myTable"
, datasource = "myDatasource"
, fieldList = "productID,title,price"
, sortBy = "#url.sort#"
, orderBy = "#url.ord#"
);
</cfscript>
<cfoutput>#result#</cfoutput>

3 thoughts on “Query Sorted Results in HTML Table

  1. You know there may be a simpler way. Stuart Langridge has a great table sort script that uses a combination of non-obtrusive javascript and css for table sorting and is very simple to implement.
    http://kryogenix.org/code/browser/sorttable/

    It allows for custom sort keys, non-sorting columns, allows rows at the very bottom of the table not to be sorted (for totals etc), as well as quite a few other options. Well worth checking out.

    regards,
    larry

Leave a Reply