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