Search code examples
coldfusioncoldfusion-9cfquery

Using dynamic variables in query

I have a page that has an variable number of input fields (this number of fields is saved in the database). Upon submit, the information in these fields is to be saved into a database. The names of the fields are looped through and the current loop number is attached to the end of the field. I then save this into a variable and use the variable in the query. It's getting the correct information from the variable, but isn't actually evaluating the form fields. For instance, the values put into the form fields are:

"#FORM.TEST_LOCATION_1#=two"<br>
"#FORM.TEST_LOCATION_2#=four"<br>
"#FORM.TEST_LOCATION_3#=six"<br>
"#FORM.TEST_LOCATION_4#=eight"<br>
"#FORM.TEST_LOCATION_5#=ten"<br>
"#FORM.TEST_NAME_1#=one"<br>
"#FORM.TEST_NAME_2#=three"<br>
"#FORM.TEST_NAME_3#=five"<br>
"#FORM.TEST_NAME_4#=seven"<br>
"#FORM.TEST_NAME_5#=nine"<br>

The variable that is used in the query gets:

test_location_1 = '#form.test_location_1#', test_name_1 = '#form.test_name_1#', test_location_2 = '#form.test_location_2#', test_name_2 = '#form.test_name_2#', test_location_3 = '#form.test_location_3#', test_name_3 = '#form.test_name_3#', test_location_4 = '#form.test_location_4#', test_name_4 = '#form.test_name_4#', test_location_5 = '#form.test_location_5#', test_name_5 = '#form.test_name_5#',

but instead of putting the values actually entered in the input field in to database, it puts:

"#form.test_location_1#"
"#form.test_location_2#"
"#form.test_location_3#"
"#form.test_name_1#"
"#form.test_name_2#"
"#form.test_name_3#"
etc

The code I'm using right now is:

 <cfset session.updque = ''>
    <cfloop from="1" to="#session.test_numfields#" index="numf">
      <cfset session.updque &= "test_location_" & #numf# &" = '##form.test_location_" & #numf# & "##', ">
      <cfset session.updque &= "test_name_" & #numf# &" = '##form.test_name_" & #numf# & "##', ">
    </cfloop>
    <cfquery DATASOURCE="#ODSN#" NAME="uptest" >  
      UPDATE redbook_test SET
        <cfoutput>#PreserveSingleQuotes(updque)#</cfoutput>
        test_date_last_mod='#datecompleted#',
        test_status='C', 
        where buildno = '#session.buildno#' 
    </CFQUERY>

What do I need to do to actually get the form variable saved into the database??

Solution

  • This sort of syntax will get you started.

    update redbook_test
set test_date_last_mod <cfqueryparam value="#test_date_last_mod#">
, test_status='C'
<cfloop list="#form.fieldnames#" index="ThisField">
<cfif left(ThisField, 5) is "test_">
, #ThisField# = <cfqueryparam value = "#form[ThisField]#">
</cfif>
</cfloop>
where buildno = <cfqueryparam value='#session.buildno#'>

    It's not complete. You have to consider how to handle empty strings, different datatypes, and such.