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