Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

Sorting Multidimensional Arrays

Sorting Multidimensional Arrays

In my last article we looked at how to sort multidimensional arrays by creating a second single-dimensional array that is used as a key. The focus of this article is how to sort multidimensional arrays by creating a query object that can be sorted in the same way you would an ordered result set from a database. In truth, this article will demonstrate two methods: one for ColdFusion 5.0 and a second, far superior method using the <CFFUNCTION> tag introduced with ColdFusion MX.

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">

For each of the two new methods three variables will need to be created:

<cfset colArray = session.masdailysales>
<cfset colNames = "product,quantity,price,name,type">
<cfset colSort = #sort_column#>

The colArray variable will be a local copy of the original array, colNames will be the field names assigned to each column of the table being created, and colSort (passed as a URL variable, named sort_column, from the column header hyperlinks described below) will be the number of the column that the completed table will be sorted on.

Defining the colSort Variable
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 that will be used to create the colSort variable.

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

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

When the page is reloaded, the following code will set the column number that is to be sorted. The IsNumeric() function is used to ensure that a numeric value is used. This defaults to 1 if not defined, blank, less than zero, greater than the number of columns (5 in this example), or non-numeric.

<cfif IsDefined("url.sort_column") AND IsNumeric(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>

ColdFusion 5 Coding
Using the string colNames, an array will be created that contains the field names for each of the elements of the original array:

<cfset s_order = arraynew(1)>
<cfloop from="1" to="#ListLen(colNames)#" index="i">
<cfset s_order[i] = ListGetAt(colNames,i)>
</cfloop>

This coding will create the array to the right.

The first step in querying arrays is creating a query object. Query objects are data structures that hold data, such as the information from our original array. Using the QueryNew(), QueryAddRow(), and QuerySetCell() functions, the following code illustrates how to create a query object called tempquery:

<cfset tempquery = QueryNew(#colNames#)>
<cfset qRow = QueryAddRow(tempquery, #ArrayLen(colArray)#)>
<cfloop from="1" to="#ArrayLen(colArray)#"
index="i">
<cfloop from="1" to="#ArrayLen(s_order)#" index="j">
<cfset temp = QuerySetCell(tempquery,
"#s_order[j]#",
colArray[i][j], i)>
</cfloop>
</cfloop>

The parameter for the QueryNew() function is the string colNames. This string contains the equivalent of field names in a database table or column headers. Next we’ll add the same number of rows to the tempquery array as there are in the original array. The QueryAddRow() function is used to add these rows with tempquery and the length of the original array used as parameters.

Using a nested loop sequence and the QuerySetCell() function, the tempquery array is populated with the data from the original array, only now we have a query object:

 

The final step is executing a <CFQUERY>, selecting from tempquery and setting the order to the column specified by the colSort variable:

<cfquery dbtype="query" name="sortquery">
select * from tempquery
order by #s_order[colSort]#
</cfquery>

Less code is required, execution time is slightly faster, and you get the same results as you would using the coding in my previous article. With a little experimentation this code can be easily converted to a custom tag.

ColdFusion MX Coding
ColdFusion MX introduced <CFFUNCTION>, making it no longer necessary to use <CFSCRIPT>. With <CFFUNCTION> you can now create user-defined functions that use ColdFusion tags in their body, including <CFQUERY> and <CFSTOREDPROC>.

By changing the local variables to parameters and using the sortquery object as a return value, the following code will create a custom function that can be utilized in any ColdFusion MX application. The coding is shown below:

<cffunction name="MDArraySort" Returntype="query">
<!--- Create an arguments collection for arguments passed to function colArray – array being sorted colNames – column/field names for the table being created colSort – which column to sort on --->
<cfargument name="colArray" type="array" required="true">
<cfargument name="colNames" type="string" required="true">
<cfargument name="colSort" type="string" required="true">
<!--- Declare variable collection used in function as local --->
<cfset var s_order = arraynew(1)>
<cfset var tempquery = "">
<cfset var qRow = "">
<cfset var temp = "">
<!--- Create array that contains the column/field names --->
<cfloop from="1" to="#ListLen(arguments.colNames)#" index="i">
<cfset s_order[i] = ListGetAt(arguments.colNames,i)>
</cfloop>
<cfset tempquery = querynew(#arguments.colNames#)>
<cfset qRow = QueryAddRow(tempquery,
#ArrayLen(arguments.colArray)#)>
<!--- Populate the query table --->
<cfloop from="1" to="#arraylen(arguments.colArray)#" index="i">
<cfloop from="1" to="#arraylen(s_order)#" index="j">
<cfset temp = QuerySetCell(tempquery, "#s_order[j]#",
arguments.colArray[i][j], i)>
</cfloop>
</cfloop>
<!--- perform a select that sorts the new table --->
<cfquery dbtype="query" name="sortquery">
select * from tempquery
order by #s_order[arguments.colSort]#
</cfquery>
<cfreturn sortquery>
</cffunction>

With the function created and made available to all applications, the following code will create a query object that can be used to display the information to the user, in the order they select. <cfset t1 = MDArraySort(session.masdailysales,"product,quantity,price, name,type","#colOrder#")>

Far superior to the previously described routines, the ColdFusion MX solution is generic, and can be utilized in any ColdFusion MX application without modification.

Conclusion
As shown in these two articles, working with arrays can add flexibility, increase the speed of a dynamic Web page by reducing the number of calls to a data source, add power to your Web application, and ultimately make the end user of your Web application very happy. In closing, remember, when old man Murphy comes to visit, always give him a warm welcome and learn from the lessons he strives to teach.

More Stories By Richard Gorremans

For the past four yers, Richard Gorremans has been working for EDFUND, the nonprofit side of the Student aid Commission, located in Rancho Cordova, California. A senior software engineer with over 13 years in the business, he has been working as a technical lead producing Web-based products tht enable borrowers, lenders, and schools to view and maintain student loan information via the Web.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
SYS-CON Events announced today that Yuasa System will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Yuasa System is introducing a multi-purpose endurance testing system for flexible displays, OLED devices, flexible substrates, flat cables, and films in smartphones, wearables, automobiles, and healthcare.
SYS-CON Events announced today that Dasher Technologies will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Dasher Technologies, Inc. ® is a premier IT solution provider that delivers expert technical resources along with trusted account executives to architect and deliver complete IT solutions and services to help our clients execute their goals, plans and objectives. Since 1999, we'v...
Enterprises have taken advantage of IoT to achieve important revenue and cost advantages. What is less apparent is how incumbent enterprises operating at scale have, following success with IoT, built analytic, operations management and software development capabilities – ranging from autonomous vehicles to manageable robotics installations. They have embraced these capabilities as if they were Silicon Valley startups. As a result, many firms employ new business models that place enormous impor...
SYS-CON Events announced today that Massive Networks, that helps your business operate seamlessly with fast, reliable, and secure internet and network solutions, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. As a premier telecommunications provider, Massive Networks is headquartered out of Louisville, Colorado. With years of experience under their belt, their team of...
SYS-CON Events announced today that TidalScale, a leading provider of systems and services, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TidalScale has been involved in shaping the computing landscape. They've designed, developed and deployed some of the most important and successful systems and services in the history of the computing industry - internet, Ethernet, operating s...
SYS-CON Events announced today that Taica will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Taica manufacturers Alpha-GEL brand silicone components and materials, which maintain outstanding performance over a wide temperature range -40C to +200C. For more information, visit http://www.taica.co.jp/english/.
SYS-CON Events announced today that MIRAI Inc. will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MIRAI Inc. are IT consultants from the public sector whose mission is to solve social issues by technology and innovation and to create a meaningful future for people.
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
SYS-CON Events announced today that TidalScale will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. TidalScale is the leading provider of Software-Defined Servers that bring flexibility to modern data centers by right-sizing servers on the fly to fit any data set or workload. TidalScale’s award-winning inverse hypervisor technology combines multiple commodity servers (including their ass...
Join IBM November 1 at 21st Cloud Expo at the Santa Clara Convention Center in Santa Clara, CA, and learn how IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Cognitive analysis impacts today’s systems with unparalleled ability that were previously available only to manned, back-end operations. Thanks to cloud processing, IBM Watson can bring cognitive services and AI to intelligent, unmanned systems. Imagine a robot vacuum that becomes your personal assistant tha...
Widespread fragmentation is stalling the growth of the IIoT and making it difficult for partners to work together. The number of software platforms, apps, hardware and connectivity standards is creating paralysis among businesses that are afraid of being locked into a solution. EdgeX Foundry is unifying the community around a common IoT edge framework and an ecosystem of interoperable components.
As popularity of the smart home is growing and continues to go mainstream, technological factors play a greater role. The IoT protocol houses the interoperability battery consumption, security, and configuration of a smart home device, and it can be difficult for companies to choose the right kind for their product. For both DIY and professionally installed smart homes, developers need to consider each of these elements for their product to be successful in the market and current smart homes.
Infoblox delivers Actionable Network Intelligence to enterprise, government, and service provider customers around the world. They are the industry leader in DNS, DHCP, and IP address management, the category known as DDI. We empower thousands of organizations to control and secure their networks from the core-enabling them to increase efficiency and visibility, improve customer service, and meet compliance requirements.
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
SYS-CON Events announced today that mruby Forum will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. mruby is the lightweight implementation of the Ruby language. We introduce mruby and the mruby IoT framework that enhances development productivity. For more information, visit http://forum.mruby.org/.
Digital transformation is changing the face of business. The IDC predicts that enterprises will commit to a massive new scale of digital transformation, to stake out leadership positions in the "digital transformation economy." Accordingly, attendees at the upcoming Cloud Expo | @ThingsExpo at the Santa Clara Convention Center in Santa Clara, CA, Oct 31-Nov 2, will find fresh new content in a new track called Enterprise Cloud & Digital Transformation.
Most technology leaders, contemporary and from the hardware era, are reshaping their businesses to do software. They hope to capture value from emerging technologies such as IoT, SDN, and AI. Ultimately, irrespective of the vertical, it is about deriving value from independent software applications participating in an ecosystem as one comprehensive solution. In his session at @ThingsExpo, Kausik Sridhar, founder and CTO of Pulzze Systems, will discuss how given the magnitude of today's applicati...
Smart cities have the potential to change our lives at so many levels for citizens: less pollution, reduced parking obstacles, better health, education and more energy savings. Real-time data streaming and the Internet of Things (IoT) possess the power to turn this vision into a reality. However, most organizations today are building their data infrastructure to focus solely on addressing immediate business needs vs. a platform capable of quickly adapting emerging technologies to address future ...
SYS-CON Events announced today that NetApp has been named “Bronze Sponsor” of SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. NetApp is the data authority for hybrid cloud. NetApp provides a full range of hybrid cloud data services that simplify management of applications and data across cloud and on-premises environments to accelerate digital transformation. Together with their partners, NetApp emp...
In a recent survey, Sumo Logic surveyed 1,500 customers who employ cloud services such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). According to the survey, a quarter of the respondents have already deployed Docker containers and nearly as many (23 percent) are employing the AWS Lambda serverless computing framework. It’s clear: serverless is here to stay. The adoption does come with some needed changes, within both application development and operations. Tha...