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">
Archived Comments
You could do it a little bit more dynamic, if you wanted to be able to use the same code when you didnt know what all of the columns were (or didnt want to hardcode them in anyway) by using the columnlist attribute of the query recordset. I suggest turning it into an array. Some psuedo code:
columnsarray = listtoarray(query.columnlist)
then you could access a specific row by doing something like
rownum = 1
loop from=1 to=arraylen(columnsarray) index=i
query[columnsarray[i]][rownum]
/loop
you may have to fiddle with it a little bit, but it will get you there, and without using evaluate()
Just a follow up, youre going to have to do it like:
query["#columnsarray[i]#"][rownum]
just so you know.
You sure? I think your first way would work fine too.
possibly... im pulling this out of some code I did a few months back so Im not really sure...
yep, youre right ray:
query[columnsarray[i]][rownum] will work just fine, no "# #" needed.