Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ColdFusion database loop on similar column names

    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:

    Code:
    #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:

    Code:
    <cfloop index="x" from="1" to="3" step="1">
    <p>#objective[x]#</p>
    </cfloop>
    Any ideas?

    Thanks in advance for your time.

  • #2
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    What's your query name? I believe you can use the query name, and then grab the columns using array notation.

    Ex:
    Code:
    <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

  • #3
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

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

  • #4
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    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:
    Code:
    <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

  • #5
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I had to modify your code a little, but it worked!

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

  • #6
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Ah whoops, left a </cfoutput> at the end instead of a </cfloop> But I'm glad it worked!

    All the best,
    Greg


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •