Richard Gorremans

One of the most common requests made by users when they see data displayed on a Web page is that they want to be able to view the information sorted by columns. Working with a database makes this request fairly simple; working with arrays is, or at least was, a problem.

Why use an array? No ColdFusion programmer would volunteer to do such a crazy thing. In a recent project, where our team was pulling loan information resultsets from a mainframe repository, I did just that. The loan information was being stored in a data structure that contained multidimensional arrays created with WDDX calls to the mainframe. We always want to keep the users happy, so the Internet was searched, several routines for sorting multidimensional arrays were found and tested, and the users were promised that they would be able to dynamically display the results in different sorted orders by clicking on the column headers. Everyone was happy and the team was ready to code.

We Forgot to Account for Murphy's Law
Let's take a look at how it was to be done...

The first step is to build a small multidimensional array. The example code below shows daily sales for a fruit stand:

<cfset Session.masDailySales = arraynew(2)>
<cfset Session.masDailySales[1][1] = "Apples">
<cfset Session.masDailySales[1][2] = "1">
<cfset Session.masDailySales[1][3] = "9.95">
<cfset Session.masDailySales[1][4] = "Michael">
<cfset Session.masDailySales[1][5] = "Cash">

<cfset Session.masDailySales[2][1] = "Oranges">
<cfset Session.masDailySales[2][2] = "1">
<cfset Session.masDailySales[2][3] = "6.95">
<cfset Session.masDailySales[2][4] = "Joanne">
<cfset Session.masDailySales[2][5] = "Check">

<cfset Session.masDailySales[3][1] = "Peaches">
<cfset Session.masDailySales[3][2] = "4">
<cfset Session.masDailySales[3][3] = "8.95">
<cfset Session.masDailySales[3][4] = "Michael">
<cfset Session.masDailySales[3][5] = "Credit">

In the examples found on sorting arrays, they suggested creating a second array (single dimension), populating that array with the column of information to be sorted, sorting that array using arraysort(), and then creating a new multidimensional array by comparing the information in the single-dimension array to the information in the original multidimensional array. In theory this will work. The problem with this approach is that if you have multiple rows with the same information, your resulting sorted array has incorrect information.

For example, you want to sort on the quantity purchased. Using the sorting method commonly suggested, you end up with the following array:

masDailySales[1][1] = "Apples"
masDailySales[1][2] = "1";
masDailySales[1][3] = "9.95"
masDailySales[1][4] = "Michael"
masDailySales[1][5] = "Cash"

masDailySales[2][1] = "Apples"
masDailySales[2][2] = "1";
masDailySales[2][3] = "9.95"
masDailySales[2][4] = "Michael"
masDailySales[2][5] = "Cash"

masDailySales[3][1] = "Peaches"
masDailySales[3][2] = "4"
masDailySales[3][3] = "8.95"
masDailySales[3][4] = "Michael"
masDailySales[3][5] = "Credit"

The information on the oranges was replaced with the information about the apples because the sorted fields both had the number 1 in them. The match/replace routines always found the first element to match, not the correct element. The users were not going to be very happy with this.

Now, let's take a look at how it was changed...

When creating the initial array, a row is added to each element that will have no value:

<cfset Session.masDailySales[1][6] = "">
<cfset Session.masDailySales[2][6] = "">
<cfset Session.masDailySales[3][6] = "">

The new row will be used as the sorting row. Each time the page is called, the new row is populated with the information from the column you want to sort on. To ensure that the information is always unique, the index key for the looping routine used to create the single-dimension array will be concatenated to the information stored in the new row:

<cfloop from="1" to="#arrayLen(Session.masDailySales)#" index="i">
<cfswitch expression="#sort_column#">
<cfcase value="1">
<cfset Session.masDailySales[i][6] =
"#Session.masDailySales[i][1]# - #i#">
</cfcase>
<cfcase value="2">
<cfset Session.masDailySales[i][6] =
"#Session.masDailySales[i][2]# - #i#">
</cfcase>
<cfcase value="3">
<cfset Session.masDailySales[i][6] =
"#Session.masDailySales[i][3]# - #i#">
</cfcase>
<cfcase value="4">
<cfset Session.masDailySales[i][6] =
"#Session.masDailySales[i][4]# - #i#">
</cfcase>
<cfcase value="5">
<cfset Session.masDailySales[i][6] =
"#Session.masDailySales[i][5]# - #i#">
</cfcase>
</cfswitch>
</cfloop>

Indicating the Sort Column and Order
Before I get too far ahead of myself, it will be best to back up a bit and explain how the sort_column is set.

Each of the column titles is displayed to the user as a hyperlink. The hyperlinks will each call the page again, and will pass the column number as sort_column. The code below shows how it will default to one (1). Each time the page is called it looks for this information and sets the sort_column URL parameter.

As an example, the title for the Quantity would be coded as:

<a href="MultArraySort.cfm?sort_order=#sort_order#&sort_column=2">
Quantity</a>

When the page is reloaded the following code will set the column number that is to be sorted.

<cfif IsDefined("url.sort_column") AND url.sort_column GT 0 and
url.sort_column LT 5>
<cfset sort_column = url.sort_column>
<cfelse>
<cfset sort_column = 1>
</cfif>

To facilitate the switching between Ascending and Descending order, another URL parameter is passed. This parameter will switch the sort_order variable between Asc and Desc, using Asc as the default direction.

<cfif IsDefined("url.sort_order")>
<cfif sort_order EQ "Asc">
<cfset sort_order = "Desc">
<cfelse>
<cfset sort_order = "Asc">
</cfif>
<cfelse>
<cfset sort_order = "Asc">
</cfif>

Back to the Sort Processing
We now have a column of unique information that can be used as a reference key between the original array and the sorted array. Using the new row, a temporary single-dimension array, tempmasarray, is created and sorted in the specified order (Asc or Desc).

<cfset tempmasarray = arraynew(1)>
<cfloop from="1" to="#arraylen(session.masDailySales)#" index="i">
<cfset tempmasarray[i] = session.masDailySales[i][6]>
</cfloop>
<cfset arraysort(tempmasarray, "textnocase", sort_order)>

The last step is to create a new multidimensional array using the tempmasarray as the key. Two looping routines are used to perform this function. The first loop steps through the tempmasarray. The second loop takes the values from the tempmasarray and searches for a match in the original array. When a match is found, the information from that element is appended to the new array. When the looping is completed, the sortedsalesarray is an exact duplicate of the original array, sorted in the desired order.

<cfset sortedsalesarray = arraynew(2)>

<cfloop from="1" to="#arraylen(tempmasarray)#" index="i">
<cfloop from="1" to="#arraylen(session.masDailySales)#" index="i2">
<cfif tempmasarray[i] eq session.masDailySales[i2][6]>
<cfset sortedsalesarray[i] = session.masDailySales[i2]>
<cfbreak>
</cfif>
</cfloop>
</cfloop>

Of course, still another solution to this problem would be to store the data not in arrays to be sorted, but instead, in a query resultset (using the functions QueryNew and QuerySetCell and CF's query of queries functionality). Whether that approach will perform better will depend on the data and your processing, but it may be worth investigating. This additional solution will be the topic of my next article.

Summary
The functionality described in this article is not something a ColdFusion programmer will deal with on a regular basis, and is not recommended if you have the ability to make calls to a database and receive a resultset that's sorted. It's always recommended that you leave data processing to a database engine whenever possible. But consider if your information is stored on another server and you're using WDDX routines or perhaps Web services to retrieve resultsets and you want to sort that data; then this routine will be very helpful in reducing the number of calls to those servers by sorting the data on the local ColdFusion server.