...

View Full Version : ColdFusion database loop on similar column names



brorobo
03-29-2011, 03:06 PM
I have a Microsoft SQL 2000 database and a table with column names Objective1, Objective2, Objective3, (etc.) and I want to loop through each of these columns in the output so that they display:


#Objective1#
#Objective2#
#Objective3#
(etc.)

Is there a way to loop through the name of the column and the number?

Something like this obviously won't work, but it's kind of what I'd like to do:


<cfloop index="x" from="1" to="3" step="1">
<p>#objective[x]#</p>
</cfloop>


Any ideas?

Thanks in advance for your time.

Gjslick
03-30-2011, 06:41 AM
What's your query name? I believe you can use the query name, and then grab the columns using array notation.

Ex:

<cfquery name="myQuery" datasource="dsn">
SELECT * FROM table
</cfquery>

<cfoutput query="myQuery">
<cfloop from="1" to="3" index="i">
#myQuery[ 'objective' & i ]#<br>
</cfloop>
</cfoutput>


Try that and let me know how it goes.

-Greg

brorobo
03-30-2011, 09:31 PM
Thanks for replying

I tried the code you provided (my query name is GetObjectives) and I get an error:
Complex object types cannot be converted to simple values.


<cfoutput query="GetObjectives">
<cfloop from="1" to="3" index="i">
#GetObjectives[ 'objective' & i ]#<br>
</cfloop>
</cfoutput>

Gjslick
03-30-2011, 11:37 PM
Oh man, sorry about that. I don't have a CF server running at the moment to test on. But this code of going directly into the query resultset data should work:

<cfquery name="myQuery" datasource="dsn">
SELECT * FROM table
</cfquery>

<cfset numRecords = myQuery.recordCount>
<cfloop from="1" to="#numRecords#" index="i">
<cfloop from="1" to="3" index="j">
#myQuery[ 'objective' & j ][ i ]#<br>
</cfloop>
</cfoutput>


Hope that works, but if not, let me know and I'll re-install a local server when I get home :)

-Greg

brorobo
04-01-2011, 05:03 PM
I had to modify your code a little, but it worked!


<cfoutput>
<cfloop from="1" to="#numRecords#" index="i">
<cfloop from="1" to="3" index="j">
#GetObjectives[ 'objective' & j ][ i ]#<br />
</cfloop>
</cfloop>
</cfoutput>

Thanks!

Gjslick
04-01-2011, 06:16 PM
Ah whoops, left a </cfoutput> at the end instead of a </cfloop> :p But I'm glad it worked!

All the best,
Greg



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum