8 Replies Latest reply on Mar 13, 2007 4:45 AM by Stefan_K.

    Dynamically Evaluating the value of column list

    cutie369 Level 1
      I am trying to create and auto manitaining form based on columlist coming back from a query. How can I get the dynamic value of the items. I played around trying to do a query of queries. Somehow, it is just not working. Can someone please tell me how to get the value to place it in the input box.

      <cfif isdefined("theSQLQuery2")><cfdump label="SQL" var="#theSQLQuery2#"></cfif>
      <cfquery dbtype="query" name="getthis">
      Select * from theSQLQuery2
      </cfquery>
      <cfdump var="#getthis#">
      <h3>Create configuration settings</h3>
      <table width="800" border="1" cellspacing="0" cellpadding="0">
      <cfloop list="#theSQLQuery2.ColumnList#" index="ColumnList">
      <tr>

      <th scope="row"><cfoutput>#ColumnList#</cfoutput></th>
      <td><input name="<cfoutput>#ColumnList#</cfoutput>" type="text" value="" size="30"></td>

      </tr>
      </cfloop>
      </table>
        • 1. Re: Dynamically Evaluating the value of column list
          Level 7
          what is it you are trying to do exactly?
          will <cfgrid> be your solution?
          if not, try giving your cfloop's index a different name (change
          index="ColumnList" to, say, index="ColumnName") - you are possibly
          creating a variable confusion there by using same name for index as a
          built-in CF var...
          --
          Azadi Saryev
          Sabai-dee.com
          Vientiane, Laos
          http://www.sabai-dee.com
          • 2. Re: Dynamically Evaluating the value of column list
            cutie369 Level 1
            This worked for me.

            <cfif isdefined("theSQLQuery2")><cfdump label="SQL" var="#theSQLQuery2#"></cfif>
            <cfquery dbtype="query" name="getthis">
            Select * from theSQLQuery2
            </cfquery>
            <cfdump var="#getthis#">
            <h3>Create configuration settings</h3>
            <table width="800" border="1" cellspacing="0" cellpadding="0">
            <cfloop list="#theSQLQuery2.ColumnList#" index="ColumnName">
            <tr>

            <th scope="row"><cfoutput>#ColumnName#</cfoutput></th>
            <td><input name="<cfoutput>#ColumnName#</cfoutput>" <cfif ColumnName EQ "CONFIGID">type="hidden"<cfelse>type="text"</cfif> value="<cfoutput>#Evaluate("theSQLQuery2." & ColumnName)#</cfoutput>" size="30"></td>

            </tr>
            </cfloop>
            </table>

            I know I am taking a performance hit with evaluate. Is there a better way of doing this.
            • 3. Re: Dynamically Evaluating the value of column list
              insuractive Level 3
              I believe query variables can be accessed using the following notation:

              QueryName["QueryColumn"][row]

              So in your case, you might be able to use:
              <input name="<cfoutput>#ColumnName#</cfoutput>" <cfif ColumnName EQ "CONFIGID">type="hidden"<cfelse>type="text"</cfif> value="<cfoutput>#theSQLQuery2[ColumnName][1]#</cfoutput>" size="30">
              • 4. Re: Dynamically Evaluating the value of column list
                cutie369 Level 1
                That worked nicely. Thanks for the tip. Now another question. How might I sort those column names?
                • 5. Re: Dynamically Evaluating the value of column list
                  Level 7
                  cutie369 wrote:
                  > That worked nicely. Thanks for the tip. Now another question. How might I sort those column names?

                  create a temp array (i.e. <cfset myArr = ArrayNew(1))
                  use ListToArray() on your list
                  use ArraySort()
                  cfloop over the array or use ArrayToList() to convert back to list and
                  loop over it

                  look up those functions in the reference/livedocs

                  --
                  Azadi Saryev
                  Sabai-dee.com
                  Vientiane, Laos
                  http://www.sabai-dee.com
                  • 6. Re: Dynamically Evaluating the value of column list
                    Level 7
                    Correction: no need for the first step:
                    > create a temp array (i.e. <cfset myArr = ArrayNew(1))

                    --
                    Azadi Saryev
                    Sabai-dee.com
                    Vientiane, Laos
                    http://www.sabai-dee.com
                    • 7. Re: Dynamically Evaluating the value of column list
                      Dan Bracuk Level 5
                      quote:

                      Originally posted by: cutie369
                      This worked for me.

                      <cfif isdefined("theSQLQuery2")><cfdump label="SQL" var="#theSQLQuery2#"></cfif>
                      <cfquery dbtype="query" name="getthis">
                      Select * from theSQLQuery2
                      </cfquery>
                      <cfdump var="#getthis#">
                      <h3>Create configuration settings</h3>
                      <table width="800" border="1" cellspacing="0" cellpadding="0">
                      <cfloop list="#theSQLQuery2.ColumnList#" index="ColumnName">
                      <tr>

                      <th scope="row"><cfoutput>#ColumnName#</cfoutput></th>
                      <td><input name="<cfoutput>#ColumnName#</cfoutput>" <cfif ColumnName EQ "CONFIGID">type="hidden"<cfelse>type="text"</cfif> value="<cfoutput>#Evaluate("theSQLQuery2." & ColumnName)#</cfoutput>" size="30"></td>

                      </tr>
                      </cfloop>
                      </table>

                      I know I am taking a performance hit with evaluate. Is there a better way of doing this.

                      You are also taking a performance hit by returning the entire table to cold fusion when all you want is the columnlist. Add "where 1 = 2" to your query to speed things up.
                      • 8. Re: Dynamically Evaluating the value of column list
                        Stefan_K. Level 1
                        The following is without evaluate() and might help. The same for cfLoop and cfOutput:

                        <cfloop query="myQuery">
                        <cfloop index="myColumn" list="#myQuery.ColumnList#">
                        #myQuery[myColumn][CurrentRow]#
                        </cfloop>
                        </cfloop>

                        <cfoutput query="myQuery">
                        <cfloop index="myColumn" list="#myQuery.ColumnList#">
                        #myQuery[myColumn][CurrentRow]#
                        </cfloop>
                        </cfoutput>