“You want to do what?”

Share this article

Ok I know this isn’t part 2 of my CFC’s for the common developer but I wanted to share with you something cool I did today. I had a query of locations which needed an extra row inserted into it. Now this sounds like an easy thing to do but there’s a catch, isn’t there always a catch. I had to have the query sort the locations alphabetically but this new row which I needed to insert had to be first in line. So my task was to:

  1. Query the database and get a listing of all locations and their respective ID’s and order them alphabetically
  2. Insert into the first row of this same query a listing for Corporate Office with an ID of -99

So here’s my query:

<cfquery name="getLocations" datasource="MyDSN">
     SELECT franchiseeName + ' (' + city + ', ' + stateProvince + ')' AS lookupdisplay, franchiseeid AS lookupkey
     FROM franchisee
ORDER BY lookupdisplay
</cfquery>

This returns a nice query result set with two columns lookupDisplay which has my location name / state and lookupKey which has the ID for this particular location. So how do I get another row into this query? Well adding a row is easy we can simple do:

<cfset QueryAddRow(getLocations,1)>

The QueryAddRow function will insert a single blank row (changing the 1 to some other number would insert that number of rows) into my result set getLocations. No in order for me to populate this row I just need to do the following:

<cfset QuerySetCell(getLocations, 'lookupDisplay',Corporate Posting (Waco, TX)')>
<cfset QuerySetCell(getLocations, 'lookupkey','-99')>

Using the QuerySetCell function I can populate my empty rows BUT there is a problem with this. The row is at the bottom of my query and I need it to be at the top. My first thought was to simply do a Query of a Queries and just reorder everything BUT my added value begins with a C and I know I have locations which start with A and B (about 8 total to be exact). So what is a developer to do? Well I start to think about things (specifically data structures) which are easy to shuffle things around in. To me arrays are the easiest data structure to sort and manipulate, but query results aren’t simple arrays. They are more like arrays with structures in each position; each row is an array positions and the columns make up the structure.

Ok so I need to write some code which will take my query and turn it into an array of structures. Easy right? Sure is thanks to www.cflib.org! If you’ve never been to CFlib you need to go there right now, it so rocks! It’s a site full of great functions for some simple, and some complex things. A quick search in the data manipulation library got me the two functions I needed ArrayOfStructuresToQuery (by David Crawford) and QueryToArrayOfStructures (by Nathan Dintenfass).

With these two functions I could turn my query into one wicked array (you should try dumping it out and take a peak for yourself!) which I could then use some code to bubble sort my last record to the top. So my next step after my query has been created and my record has been added to it is to turn my query into an array of structures like so:

<cfset getLocationsArray = QueryToArrayOfStructures(getLocations)>

Then I could do a CFLoop against this array and use the ArraySwap function to create my bubble sort like so:

<cfloop index="j" from="1" to="#ArrayLen(getLocationsArray)#">
     <cfset ArraySwap(getLocationsArray,j,ArrayLen(getLocationsArray))>
</cfloop>

Let me break down those last 3 lines for you. Line one is my CFLoop tag and in particular it’s an index loop, you programmer types will know this as a FOR loop but in ColdFusion it’s just an index loop. I set my index to j which is going to be a variable I can use inside my CFLoop tags. The from attribute is set to 1, remember in ColdFusion arrays always start with 1 while most other languages start at 0. The to attribute is actually the result of my ArrayLen() function, which is passed in the result of the QueryToArrayOfStructures functions, this ArrayLen function will return a numeric value equal to the number of array positions to ensure I don’t loop outside of my array. If I just set the to attribute to say 50 but only had 45 array positions ColdFusion would generate an ArrayIndexOutOfBounds error, basically telling me that I tired to reference something which didn’t exist.

Ok so line 2 uses the ArraySwap function to swap whatever position I’m currently at, that’s the j variable, with what ever is at the bottom of my array thereby causing whatever is at the bottom to rise up to the top. Confused? yea me too, so lets talk this through.

Ok so lets assume my array only has 5 positions in it and looks something like so:

getLocationsArray[1] = Struct A
getLocationsArray[2] = Struct B
getLocationsArray[3] = Struct C
getLocationsArray[4] = Struct D
getLocationsArray[5] = Struct E

Now the goal here is to get Struct E to be at the top and then for everything else to go A – D. So at the end of my first pass in my CFLoop my array will look like so:

getLocationsArray[1] = Struct E
getLocationsArray[2] = Struct B
getLocationsArray[3] = Struct C
getLocationsArray[4] = Struct D
getLocationsArray[5] = Struct A

Pass #2 will swap out what ever is in position 2 with whatever is in position 5

getLocationsArray[1] = Struct E
getLocationsArray[2] = Struct A
getLocationsArray[3] = Struct C
getLocationsArray[4] = Struct D
getLocationsArray[5] = Struct B

Pass #3 will swap out position 3 with positions 5

getLocationsArray[1] = Struct E
getLocationsArray[2] = Struct A
getLocationsArray[3] = Struct B
getLocationsArray[4] = Struct D
getLocationsArray[5] = Struct C

Pass #4 will swap out position 4 with position 5

getLocationsArray[1] = Struct E
getLocationsArray[2] = Struct A
getLocationsArray[3] = Struct B
getLocationsArray[4] = Struct C
getLocationsArray[5] = Struct D

Now at this point it’s good but our loop as it’s coded now will do another swap between position 5 and position 5 which results in nothing really happening.

So now you can see that I have exactly what I needed. The great thing about this is if I needed it to appear at position 3 all I have to do is change the from value in my CFLoop to 3 and the same thing will happen but it will work from 3 to 5.

Ok now that everything is the way I want it I just need to turn it back into a query result set using:

<cfset getLocations = ArrayOfStructuresToQuery(getLocationsArray)>

Using the ArrayOfStructuresToQuery function takes me back to my query which now has the data in the exact order I wanted it.

Not to shabby huh? How would you have approached the problem?

Eric JonesEric Jones
View Author
Share this article
Read Next
How to Use jQuery’s ajax() Function for Asynchronous HTTP Requests
How to Use jQuery’s ajax() Function for Asynchronous HTTP Requests
Aurelio De RosaMaria Antonietta Perna
Quick Tip: How to Align Column Rows with CSS Subgrid
Quick Tip: How to Align Column Rows with CSS Subgrid
Ralph Mason
15 Top Web Design Tools & Resources To Try in 2024
15 Top Web Design Tools & Resources To Try in 2024
SitePoint Sponsors
7 Simple Rules for Better Data Visualization
7 Simple Rules for Better Data Visualization
Mariia Merkulova
Cloudways Autonomous: Fully-Managed Scalable WordPress Hosting
Cloudways Autonomous: Fully-Managed Scalable WordPress Hosting
SitePoint Team
Best Programming Language for AI
Best Programming Language for AI
Lucero del Alba
Quick Tip: How to Add Gradient Effects and Patterns to Text
Quick Tip: How to Add Gradient Effects and Patterns to Text
Ralph Mason
Logging Made Easy: A Beginner’s Guide to Winston in Node.js
Logging Made Easy: A Beginner’s Guide to Winston in Node.js
Vultr
How to Optimize Website Content for Featured Snippets
How to Optimize Website Content for Featured Snippets
Dipen Visavadiya
Psychology and UX: Decoding the Science Behind User Clicks
Psychology and UX: Decoding the Science Behind User Clicks
Tanya Kumari
Build a Full-stack App with Node.js and htmx
Build a Full-stack App with Node.js and htmx
James Hibbard
Digital Transformation with AI: The Benefits and Challenges
Digital Transformation with AI: The Benefits and Challenges
Priyanka Prajapat
Quick Tip: Creating a Date Picker in React
Quick Tip: Creating a Date Picker in React
Dianne Pena
How to Create Interactive Animations Using React Spring
How to Create Interactive Animations Using React Spring
Yemi Ojedapo
10 Reasons to Love Google Docs
10 Reasons to Love Google Docs
Joshua KrausZain Zaidi
How to Use Magento 2 for International Ecommerce Success
How to Use Magento 2 for International Ecommerce Success
Mitul Patel
5 Exciting New JavaScript Features in 2024
5 Exciting New JavaScript Features in 2024
Olivia GibsonDarren Jones
Tools and Strategies for Efficient Web Project Management
Tools and Strategies for Efficient Web Project Management
Juliet Ofoegbu
Choosing the Best WordPress CRM Plugin for Your Business
Choosing the Best WordPress CRM Plugin for Your Business
Neve Wilkinson
ChatGPT Plugins for Marketing Success
ChatGPT Plugins for Marketing Success
Neil Jordan
Managing Static Files in Django: A Comprehensive Guide
Managing Static Files in Django: A Comprehensive Guide
Kabaki Antony
The Ultimate Guide to Choosing the Best React Website Builder
The Ultimate Guide to Choosing the Best React Website Builder
Dianne Pena
Exploring the Creative Power of CSS Filters and Blending
Exploring the Creative Power of CSS Filters and Blending
Joan Ayebola
How to Use WebSockets in Node.js to Create Real-time Apps
How to Use WebSockets in Node.js to Create Real-time Apps
Craig Buckler
Best Node.js Framework Choices for Modern App Development
Best Node.js Framework Choices for Modern App Development
Dianne Pena
SaaS Boilerplates: What They Are, And 10 of the Best
SaaS Boilerplates: What They Are, And 10 of the Best
Zain Zaidi
Understanding Cookies and Sessions in React
Understanding Cookies and Sessions in React
Blessing Ene Anyebe
Enhanced Internationalization (i18n) in Next.js 14
Enhanced Internationalization (i18n) in Next.js 14
Emmanuel Onyeyaforo
Essential React Native Performance Tips and Tricks
Essential React Native Performance Tips and Tricks
Shaik Mukthahar
How to Use Server-sent Events in Node.js
How to Use Server-sent Events in Node.js
Craig Buckler
Five Simple Ways to Boost a WooCommerce Site’s Performance
Five Simple Ways to Boost a WooCommerce Site’s Performance
Palash Ghosh
Elevate Your Online Store with Top WooCommerce Plugins
Elevate Your Online Store with Top WooCommerce Plugins
Dianne Pena
Unleash Your Website’s Potential: Top 5 SEO Tools of 2024
Unleash Your Website’s Potential: Top 5 SEO Tools of 2024
Dianne Pena
How to Build a Chat Interface using Gradio & Vultr Cloud GPU
How to Build a Chat Interface using Gradio & Vultr Cloud GPU
Vultr
Enhance Your React Apps with ShadCn Utilities and Components
Enhance Your React Apps with ShadCn Utilities and Components
David Jaja
10 Best Create React App Alternatives for Different Use Cases
10 Best Create React App Alternatives for Different Use Cases
Zain Zaidi
Control Lazy Load, Infinite Scroll and Animations in React
Control Lazy Load, Infinite Scroll and Animations in React
Blessing Ene Anyebe
Building a Research Assistant Tool with AI and JavaScript
Building a Research Assistant Tool with AI and JavaScript
Mahmud Adeleye
Understanding React useEffect
Understanding React useEffect
Dianne Pena
Web Design Trends to Watch in 2024
Web Design Trends to Watch in 2024
Juliet Ofoegbu
Building a 3D Card Flip Animation with CSS Houdini
Building a 3D Card Flip Animation with CSS Houdini
Fred Zugs
How to Use ChatGPT in an Unavailable Country
How to Use ChatGPT in an Unavailable Country
Dianne Pena
An Introduction to Node.js Multithreading
An Introduction to Node.js Multithreading
Craig Buckler
How to Boost WordPress Security and Protect Your SEO Ranking
How to Boost WordPress Security and Protect Your SEO Ranking
Jaya Iyer
Understanding How ChatGPT Maintains Context
Understanding How ChatGPT Maintains Context
Dianne Pena
Building Interactive Data Visualizations with D3.js and React
Building Interactive Data Visualizations with D3.js and React
Oluwabusayo Jacobs
JavaScript vs Python: Which One Should You Learn First?
JavaScript vs Python: Which One Should You Learn First?
Olivia GibsonDarren Jones
13 Best Books, Courses and Communities for Learning React
13 Best Books, Courses and Communities for Learning React
Zain Zaidi
5 jQuery.each() Function Examples
5 jQuery.each() Function Examples
Florian RapplJames Hibbard
Implementing User Authentication in React Apps with Appwrite
Implementing User Authentication in React Apps with Appwrite
Yemi Ojedapo
AI-Powered Search Engine With Milvus Vector Database on Vultr
AI-Powered Search Engine With Milvus Vector Database on Vultr
Vultr
Understanding Signals in Django
Understanding Signals in Django
Kabaki Antony
Why React Icons May Be the Only Icon Library You Need
Why React Icons May Be the Only Icon Library You Need
Zain Zaidi
View Transitions in Astro
View Transitions in Astro
Tamas Piros
Getting Started with Content Collections in Astro
Getting Started with Content Collections in Astro
Tamas Piros
What Does the Java Virtual Machine Do All Day?
What Does the Java Virtual Machine Do All Day?
Peter Kessler
Become a Freelance Web Developer on Fiverr: Ultimate Guide
Become a Freelance Web Developer on Fiverr: Ultimate Guide
Mayank Singh
Layouts in Astro
Layouts in Astro
Tamas Piros
.NET 8: Blazor Render Modes Explained
.NET 8: Blazor Render Modes Explained
Peter De Tender
Mastering Node CSV
Mastering Node CSV
Dianne Pena
A Beginner’s Guide to SvelteKit
A Beginner’s Guide to SvelteKit
Erik KückelheimSimon Holthausen
Brighten Up Your Astro Site with KwesForms and Rive
Brighten Up Your Astro Site with KwesForms and Rive
Paul Scanlon
Which Programming Language Should I Learn First in 2024?
Which Programming Language Should I Learn First in 2024?
Joel Falconer
Managing PHP Versions with Laravel Herd
Managing PHP Versions with Laravel Herd
Dianne Pena
Accelerating the Cloud: The Final Steps
Accelerating the Cloud: The Final Steps
Dave Neary
An Alphebetized List of MIME Types
An Alphebetized List of MIME Types
Dianne Pena
The Best PHP Frameworks for 2024
The Best PHP Frameworks for 2024
Claudio Ribeiro
11 Best WordPress Themes for Developers & Designers in 2024
11 Best WordPress Themes for Developers & Designers in 2024
SitePoint Sponsors
Top 9 Best WordPress AI Plugins of 2024
Top 9 Best WordPress AI Plugins of 2024
Dianne Pena
20+ Tools for Node.js Development in 2024
20+ Tools for Node.js Development in 2024
Dianne Pena
The Best Figma Plugins to Enhance Your Design Workflow in 2024
The Best Figma Plugins to Enhance Your Design Workflow in 2024
Dianne Pena
Harnessing the Power of Zenserp for Advanced Search Engine Parsing
Harnessing the Power of Zenserp for Advanced Search Engine Parsing
Christopher Collins
Build Your Own AI Tools in Python Using the OpenAI API
Build Your Own AI Tools in Python Using the OpenAI API
Zain Zaidi
The Best React Chart Libraries for Data Visualization in 2024
The Best React Chart Libraries for Data Visualization in 2024
Dianne Pena
7 Free AI Logo Generators to Get Started
7 Free AI Logo Generators to Get Started
Zain Zaidi
Turn Your Vue App into an Offline-ready Progressive Web App
Turn Your Vue App into an Offline-ready Progressive Web App
Imran Alam
Clean Architecture: Theming with Tailwind and CSS Variables
Clean Architecture: Theming with Tailwind and CSS Variables
Emmanuel Onyeyaforo
How to Analyze Large Text Datasets with LangChain and Python
How to Analyze Large Text Datasets with LangChain and Python
Matt Nikonorov
6 Techniques for Conditional Rendering in React, with Examples
6 Techniques for Conditional Rendering in React, with Examples
Yemi Ojedapo
Introducing STRICH: Barcode Scanning for Web Apps
Introducing STRICH: Barcode Scanning for Web Apps
Alex Suzuki
Using Nodemon and Watch in Node.js for Live Restarts
Using Nodemon and Watch in Node.js for Live Restarts
Craig Buckler
Task Automation and Debugging with AI-Powered Tools
Task Automation and Debugging with AI-Powered Tools
Timi Omoyeni
Quick Tip: Understanding React Tooltip
Quick Tip: Understanding React Tooltip
Dianne Pena
12 Outstanding AI Tools that Enhance Efficiency & Productivity
12 Outstanding AI Tools that Enhance Efficiency & Productivity
Ilija Sekulov
React Performance Optimization
React Performance Optimization
Blessing Ene Anyebe
Introducing Chatbots and Large Language Models (LLMs)
Introducing Chatbots and Large Language Models (LLMs)
Timi Omoyeni
Migrate to Ampere on OCI with Heterogeneous Kubernetes Clusters
Migrate to Ampere on OCI with Heterogeneous Kubernetes Clusters
Ampere Computing
Scale Your React App with Storybook and Chromatic
Scale Your React App with Storybook and Chromatic
Daine Mawer
10 Tips for Implementing Webflow On-page SEO
10 Tips for Implementing Webflow On-page SEO
Milan Vracar
Create Dynamic Web Experiences with Interactive SVG Animations
Create Dynamic Web Experiences with Interactive SVG Animations
Patricia Egyed
5 React Architecture Best Practices for 2024
5 React Architecture Best Practices for 2024
Sebastian Deutsch
How to Create Animated GIFs from GSAP Animations
How to Create Animated GIFs from GSAP Animations
Paul Scanlon
Aligning Teams for Effective User Onboarding Success
Aligning Teams for Effective User Onboarding Success
Himanshu Sharma
How to use the File System in Node.js
How to use the File System in Node.js
Craig Buckler
Laravel vs CodeIgniter: A Comprehensive Comparison
Laravel vs CodeIgniter: A Comprehensive Comparison
Dianne Pena
Essential Tips and Tricks for Coding HTML Emails
Essential Tips and Tricks for Coding HTML Emails
Rémi Parmentier
How to Create a Sortable and Filterable Table in React
How to Create a Sortable and Filterable Table in React
Ferenc Almasi
WooCommerce vs Wix: Which Is Best for Your Next Online Store
WooCommerce vs Wix: Which Is Best for Your Next Online Store
Priyanka Prajapati
GCC Guide for Ampere Processors
GCC Guide for Ampere Processors
John O’Neill
Navigating Data Management: Warehouses, Lakes and Lakehouses
Navigating Data Management: Warehouses, Lakes and Lakehouses
Leonid Chashnikov
Get the freshest news and resources for developers, designers and digital creators in your inbox each week