Ask a Jedi: Working with One Row from a Query

A reader asks:

How do you access the entire row when looping over a query? When I pass the query name to a DAO object that loads a bean from a single record I always get the 1st record. Any ideas are greatly appreciated.

There is no way to deal with just one row from a query, however, you can easily access any column and any row from a query using array notation. So to get the 5th row, column name, from query data, you could use:

<cfset value = data.name[5]>

If you leave off the row number, ColdFusion will assume you mean row 1, unless you are actively looping over a query in a cfloop or cfoutput.

While there is no native way to deal with just a “row”, you can use a UDF - QueryRowToStruct. This UDF will take a row from a query and convert it to a structure. Here is an example:

<cfset data = queryNew("id,name,aGe")>

<cfloop index=”x” from=”1” to=”10”> <cfset queryAddRow(data)> <cfset querySetCell(data,”id”,x)> <cfset querySetCell(data,”name”,”User #x#”)> <cfset querySetCell(data,”aGe”,randRange(20,90))> </cfloop>

<cfscript> /**

  • Makes a row of a query into a structure.
  • @param query The query to work with.
  • @param row Row number to check. Defaults to row 1.
  • @return Returns a structure.
  • @author Nathan Dintenfass (nathan@changemedia.com)
  • @version 1, December 11, 2001 */ function queryRowToStruct(query){ //by default, do this to the first row of the query var row = 1; //a var for looping var ii = 1; //the cols to loop over var cols = listToArray(query.columnList); //the struct to return var stReturn = structnew(); //if there is a second argument, use that for the row number if(arrayLen(arguments) GT 1) row = arguments[2]; //loop over the cols and build the struct from the query row for(ii = 1; ii lte arraylen(cols); ii = ii + 1){ stReturn[cols[ii]] = query[cols[ii]][row]; } //return the struct return stReturn; } </cfscript>

<cfset row5 = queryRowToStruct(data,5)> <cfdump var=”#row5#” label=”Fifth Row”> </code>

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. 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

Comments