Can I get a query row by index in ColdFusion?

Question

I want to get a specific row in a ColdFusion Query object without looping over it.

I'd like to do something like this:

<cfquery name="QueryName" datasource="ds">
SELECT *
FROM    tablename
</cfquery>

<cfset x = QueryName[5]>

But it's giving me an error saying that the query isn't indexable by "5". I know for a fact that there are more than 5 records in this query.

Solution 1

You can't get a row in CF <= 10. You have to get a specific column.

<cfset x = QueryName.columnName[5]>

It's been 8 years since I posted this answer, however. Apparently CF11 finally implemented that feature. See this answer.

Solution 2

This can now be accomplished in coldfusion 11 via QueryGetRow

<cfquery name="myQuery" result="myresult" datasource="artGallery" fetchclientinfo="yes" >
select * from art where ARTID >
<cfqueryparam value="2" cfsqltype="CF_SQL_INTEGER">
</cfquery>

<cfdump var="#myQuery#" >

<cfset data = QueryGetRow(myQuery, 1) >

<cfdump var="#data#" >

Solution 3

I think there is a simpler solution... I am guessing you know your column names and only want this column or that one. Then you don't need to put the whole row in a struct. You can reference the query by row number (remember its 1 based not 0).

yourQueryName["yourColumnName"][rowNumber]

<cfoutput>
     #mycontacts["Name"][13]#
     #mycontacts["HomePhone"][13]# 
</cfoutput>

Solution 4

You have to convert the query to a struct first:

<cfscript>
    function GetQueryRow(query, rowNumber) {
        var i = 0;
        var rowData = StructNew();
        var cols = ListToArray(query.columnList);
        for (i = 1; i lte ArrayLen(cols); i = i + 1) {
            rowData[cols[i]] = query[cols[i]][rowNumber];
        }
        return rowData;
    }
</cfscript>

<cfoutput query="yourQuery">
    <cfset theCurrentRow = GetQueryRow(yourQuery, currentRow)>
    <cfdump var="#theCurrentRow#">
</cfoutput>

Hope this points you in the right direction.

Solution 5

I know I come back to this thread any time I Google "cfquery bracket notation". Here's a function I wrote to handle this case using bracket notation. Hopefully this can help someone else too:

<cffunction name="QueryGetRow" access="public" returntype="array" hint="I return the specified row's data as an array in the correct order">
    <cfargument name="query" required="true" type="query" hint="I am the query whose row data you want">
    <cfargument name="rowNumber" required="true" hint="This is the row number of the row whose data you want">

    <cfset returnArray = []>
    <cfset valueArray = []>

    <cfset cList = ListToArray(query.ColumnList)>
    <cfloop from="1" to="#ArrayLen(cList)#" index="i">
        <cfset row = query["#cList[i]#"][rowNumber]>
        <cfset row = REReplace(row, "(,)", " ")>
        <cfset returnArray[i] = row>
        <cfset i++>
    </cfloop>   
    <cfreturn returnArray>
</cffunction>

The REReplace is optional, I have it in there to cleanse commas so that it doesn't screw up the arrayToList function later on if you have to use it.

View more solutions

Comments

[Brian Bolton]

Thanks, thats what I was looking for.

[ale]

I prefer bracket notation for both rows and columns, but either way is just as valid. QueryName["columnName"][5]. You'll need bracket notation if you want to use a variable for the column name, for instance.

[Kip]

i thought this was the only way to do this too, until i saw patrick's answer

[Mark]

Not true. You can get a row, just not using the standard CF API's. And you won't be able to access the columns directly by variable names -- you'll have to know the index. You can do myquery.getRow(0) to get the first "coldfusion.sql.imq.Row", and myrow.getColumn(0) to get the first column. Row also has a method to get an Object[] representing the entire row.

[YZE91]

Thanx! I didn't know about cflib

[williambq]

@Mark: Yes, I think that is not going to helpful most of the time for most people. These are java objects/methods and not as handy for day-to-day use: q=myQuery; r=q.getRow(3); rd=x.getRowData(); or simply rd=myQuery.getRow(3).getRowData(); But this will return an array of java-ish values (for which you will have to parse out nulls/empties) and you will lose your column headers. I can see occasional need for this but generally adds more complexity than you want.

[jyoseph]

Killer answer. I'm ashamed to say I wasn't aware of this function. cf11 keeps delivering!

[Blue]

@jyoseph Now if only they can follow up like lucee, and add a lazy="true" to the cfquery tag so I can pull thousands of rows without worrying about coldfusion trying to eat up all the memory.

[James A Mohler]

I wonder why the down votes. Has two versions of the correct answer. The Member function approach is quite clear.

[Cory Silva]

Do note that QueryGetRow will return undefined for null values where as for(row in query) loop will return empty string. cf11 keeps delivering.