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.

Seemly

CFLib.org to the rescue again: cflib.org/udf/queryGetRow . That'll let you do <cfset x = queryGetRow(QueryName,5)> – Atomizer

Answer 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.

Attest

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. – Atomizer

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. – Cromorne

@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. – Tarbes

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

Gladden

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

@Nonmoral 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. – Gladden

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

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

Coronagraph

Answer 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.

Conventicle

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

Answer 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.

Blus

Answer 6

I wanted to extract a single row from a query, and keeping the column names (of course). This is how I solved it:

<cffunction name="getQueryRow" returntype="query" output="no">
    <cfargument name="qry" type="query" required="yes">
    <cfargument name="row" type="numeric" required="yes">
    <cfset arguments.qryRow=QueryNew(arguments.qry.columnlist)>
    <cfset QueryAddRow(arguments.qryRow)>
    <cfloop list="#arguments.qry.columnlist#" index="arguments.column">
        <cfset QuerySetCell(arguments.qryRow,arguments.column,Evaluate("arguments.qry.#arguments.column#[arguments.row]"))>
    </cfloop>
    <cfreturn arguments.qryRow>
</cffunction>

Keeling

Thanx! I didn't know about cflib – Keeling

Answer 7

Methods previously described for obtaining query data by column name and row number (variables.myquery["columnName"][rowNumber]) are correct, but not convenient for getting a full row of query data.

I'm running Railo 4.1. And this is a cool solution. Too bad this can't be done the way we would want outright to get a full row of data, but the following method allows us to get what we want through a few hoops.

When you serializeJSON(variables.myquery) it changes the query to a JSON formatted cfml struct object with two items: "Columns" and "Data". Both of these are arrays of data. The "data" array is a two-dimensional array for rows and then columnar data.

The issue is that now we have an unusable string. Then if we re-serialize it it's NOT a query, but rather usable regular struct in the format described above.

Assume we already have a query variable named 'variables.myquery'. Then look at the following code:

<cfset variables.myqueryobj = deserializeJSON(serializeJSON(variables.myquery)) />

Now you get the two dimensional array by getting this:

<cfset variables.allrowsarray = variables.myqueryobj.data />

And you get one query row array by getting this:

<cfset variables.allrowsarray = variables.myqueryobj.data[1] />

OR the last row this way:

<cfset variables.allrowsarray = variables.myqueryobj.data[variables.myquery.recordCount] />

And you can get individual column values by column order number iteration:

<cfset variables.allrowsarray = variables.myqueryobj.data[1][1] />

Now this might be slow and possibly unwise with large query results, but this is a cool solution nonetheless.

Clearness

Answer 8

Check out the documentation for queryGetRow. It accepts a query object and an index of the row with the first row being referenced with the index of 1 (NOT 0) The index used this way is required to be a positive integer.

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

<!---
    This would retrieve the first record of the query
    and store the record in a struct format in the variable 'x'.
--->
<cfset x = queryGetRow(QueryName, 1) />
<!---
    This is an alternative using the member method form of queryGetRow
--->
<cfset x = QueryName.getRow(1) />

Tramel

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