ColdFusion and getting data from MySQL

Question

Hi All,

I am working on a CF site and need to get data from MySQL tables.

I can create the CFQuery fine and check for records returned, but how do I take the records returned and loop through them and get data from specific fields in each row.

I can write while if/end if, etc, I just dont recall how to get access to the data.

-JAson

Answer 1

Assuming you made a query called the_query with a firstname column:

<cfoutput query="#the_query#">
   #firstName# ... etc <br>
</cfoutput>

Answer 2

once you've defined your query within a CFQUERY, you would put your results inside a CFOUTPUT tag. You would specify the fields to display using the syntax #[query name].[field name]#.

Answer 3

Alternately, you can use the CFLOOP tag to similar effect

Say for example you wanted to sum the "price" field for those rows which have a quantity greater than zero. (imagine a shopping cart)

<CFSET TOTAL=0>
<CFLOOP QUERY="the_query">
    <CFIF quantity gt 0>
        <CFSET TOTAL=TOTAL+PRICE>
    </CFIF>
</CFLOOP>

By the way, the use of hash marks/pound signs/octothorpes is discouraged in the CFOUTPUT Query parameter

[edit:] To dump the contents of the query stack add to [cf_root]\wwwroot\WEB-INF\debug.cfm before the "<!--- :: CFTimer :: --->" line:

<CFIF IsDefined("DumpQueries") >
    <cfset DQ_Queries= QueryToArray( CFDEBUG_QUERIES ) />  
    <CFLOOP From="1" to ="#CFDEBUG_QUERIES.recordcount#" index="i">
     <CFSET DQ_Queries[i].BODY =  DQ_Queries[i].BODY >
     <CFSAVECONTENT Variable="dump_content">
     <CFDUMP Var="#DQ_Queries[i]#" Expand="NO" Label="#DQ_Queries[i].NAME#">
     </CFSAVECONTENT>
     <CFOUTPUT>#REReplaceNoCase(dump_content,"expand"">BODY</td>[[:space:]]*<TD>[[:space:]]*","expand"">BODY</td><td><PRE>")#</CFOUTPUT>  
    </CFLOOP>
</CFIF>

Answer 4

There are a number of ways to do this. as noted above, you can use cfoutput with the query attribute. You can also reference 'queryname.columnname'.

If you need to access a specific row/column, you can use array notation as such:

queryname.columnname[rownum]

(remember, CF counts from 1, not 0). For dynamic column access, you can use

queryname[columnvariable][rownum]

If your columname is an illegal variable name in CF (like 8thCol) you can modify this a bit:

queryname['8thCol'][rownum]

Hope that helps.

Answer 5

All the existing answers/comments seem to touch on different aspects, so this is an attempt to consolidate all this information into one clear explanation.
(This answer is set to community wiki - please edit as appropriate.)

---

To access a variable from a query, use:

QueryName.ColumnName[RowNum]
QueryName["ColumnName"][RowNum]

These will both work at any point after the query is created.
Both can be used inside cfoutput to display the variable, or inside cfset to assign the variable.

The second one is useful for dynamic variables, and can accept variables like so:

QueryName[DynamicColumnName][RowNum]
QueryName["Partial#DynamicName#"][RowNum]
QueryName["Partial"&DynamicName][RowNum]

---

As a convenience, instead of looping through a query manually:

<cfloop index="CurrentRow" from="1" to="#QueryName.RecordCount#">
    <cfoutput>#QueryName.ColumnName[CurrentRow]#</cfoutput>
</cfloop>

You can simply do:

<cfloop query="QueryName">
    <cfoutput>#QueryName.ColumnName[CurrentRow]#</cfoutput>
</cfloop>

And as a further shortcut, you can do:

<cfoutput query="QueryName">
    #QueryName.ColumnName[CurrentRow]#
</cfoutput>

And, when inside a <cfloop query=""> or a <cfoutput query=""> you can simply do:

<cfoutput query="QueryName">
 #ColumnName#
</cfoutput>

However, this last shortcut is only for display - if you do:

<cfset ColumnName = "NewValue" />

This will not modify the original query data. Instead, you need to do:

<cfset QueryName.ColumnName[CurrentRow] = "NewValue" />

And that will modify the results of the query (but not the value in the database).

---

To display the actual SQL query that has been run, with CF8 (also Railo and OpenBD), you can do:

<cfdump var="#QueryName#"/>

And it will show you both the results of the query and the actual SQL which has been run.

To display the actual SQL with CF7, you need to add a result="QueryInfo" to your cfquery tag, then <cfdump var="#QueryInfo#"/> will show you the SQL.