Coldfusion (Update Array using CFForm - Change values after Submit)

Question

I have a simple form. Values are loaded from a database into an array and then the entire table is displayed using cfinput and cfselect tags to allow field editing. So, I was hoping to be able to change any fields up and down the table that is displayed and then click SUBMIT and display the changed fields but nothing is ever changed. The table just reverts back to the original and the changes to the tags dissappear. I don't want to have the user update each field seperately using a seperate form. I would like to have the entire page update in one submission.

Any suggestions:

<!--- **** LOAD ARRAY FROM DATABASE  ********************************** --->

    <cfset AssignArray = ArrayNew(2)>

    <cfset i=1>
    <cfoutput query="getAssignments">
        <cfset AssignArray[i][1]="#getAssignments.Assignment#">
        <cfset AssignArray[i][2]="#getAssignments.Baylor#">
        <cfset i = i + 1>
    </cfoutput>

<!--- **** FORM WITH TABLE OF VALUES TO CHANGE  ********************************** --->

    <table border="0" cellspacing="0">
        <caption>Update Assignments</caption>

    <cfform name="formData">
    <table>
      <tr><th>#</th><th>Assignment</th><th>Name</th></tr>
      <cfloop from="1" to= "#getAssignments.RecordCount#" index="i">
         <tr>
            <td class="centercell"><cfoutput>#i#</cfoutput></td>
            <td><cfinput class="assignSize" type="text" name="Assignment"
                    maxlength="70" 
                   value="#AssignArray[i][1]#"></td>
            <td><cfselect class="assignFont" name="Name" query="getNames" 
                    display="Name" value="Baylor" selected="#TRIM(AssignArray[i][2])#">
                     <cfif AssignArray[i][2] neq "">
                         <option value="">Not Assigned</option>
                     <cfelse>
                         <option value="" selected="selected" >Not Assigned</option>
                     </cfif>
                 </cfselect>
            </td>
        </tr>
      </cfloop>
    </table>    

      <cfinput class="btnStyle" type="submit" name="submit" value="Update">
    </cfform>

    <!--- ****DUMP FORM WITH CHANGED VALUES  ************************************ --->

    <cfif IsDefined ("form.Assignment")>
        <cfif IsDefined ("form.submit")>

          <table>
          <tr><th>#</th><th>Assignment</th><th>Name</th></tr>
          <cfloop from="1" to= "#getAssignments.RecordCount#" index="i">
             <tr>
                 <td class="centercell"><cfoutput>#i#</cfoutput></td>
                 <td><cfoutput>#AssignArray[i][1]#</cfoutput></td>
                 <td><cfoutput>#AssignArray[i][2]#</cfoutput></td>
              </tr>
          </cfloop>
          </table>    
        </cfif>
    </cfif>

Answer 1

You need not copy your query into an array? Just use

<cfloop query="getAssignments">

or 

<cfoutput query="getAssignments">

Then you can compare directly as in <cfif len(trim(getAssignments.assignment)) GT 0> or whatever you need to do to make it make sense to you.

I would also avoid the use of <CFFORM>... it's generally and universally despised by most of us CF folks. A form will work great. use jquery if you want extra special effects.

In the case above if you want to see what is selected it will be located in the form scope. In other words

<cfif structKeyExists(form, 'assignment')>

<cfoutput>
#form.assignsize#
#form.name# 
</cfoutput>

</cfif>

The name of your select box is "name" which is confusing. Call it something easier to get at like "schoolName".

---

Wallace - ok I understand what you are trying to do but you need a few things. First you need some javascript (probably) to track your changes. Second you need to "name" your form elements with something unique. You'll be submitting a form with names like "assignment_#schoolid#".

It's what you are trying to do with your #currentrow# variable - but that's not tenable because it's disconnected from the DB and will eventually screw up your data. Your example is probably going to need to be scrapped and started over. This link might help.

http://www.coldfusionmuse.com/index.cfm/2011/1/5/form.loop.update.dataset

Answer 2

When I want to do what you are attempting, I organize it something like this.

Step 1 - run a query. Make sure you select the record identifier.

<cfquery name="MyQuery">
select id, field1, field2, etc
</cfquery>

Step 2 - Build your form. Use the id field to keep track of what's what.

<cfform>
<cfoutput query="MyQuery">
<input type="hidden" name="ExistingField1#id#" Value="#Field1#">
<input name = Field1#id# Value="#Field1#>
etc
</cfform>

Now, let's assume the form has been submitted. Look for changes and update if you find any.

<cfloop list="#form.fieldnames#" index = "ThisField">
<cfif left(ThisField, 6) is "Field1">
<!--- The next line will get the record identifier --->
<cfset ThisId = Right(ThisField, Len(ThisField) - 6)>
<cfif form["Field1#ThisId#"] is not form["ExistingField1#ThisId#"]
or form["Field2#ThisId#"] is not form["ExistingField2#ThisId#"]
etc>
update query goes here
three closing tags go here.

There are some other details, such as validating the submitted values, but this should get you started.

Answer 3

...and thats why I used the array because I figured the would require and update (sql update) for each row for data that is different/unique. I was hoping to beat the system by using an array with cfselect, etc.. to make it happen all at once and then send the changes in the array back to the database

It does not work that way. The CF arrays are rebuilt every time the page loads, and do not persist between requests. They exist only while you are generating the <form>. Once the <form> is sent back to the client/browser, you are disconnected from the server and the arrays are gone. To access the new values entered by the user, you must process the submitted FORM fields (not the array).

In your case, you should use unique field names. So you can keep track of each "row" or "set" of fields. One way to do that is append a counter variable to the field names. Store the number of rows in a hidden field (outside the main query loop).

<form name="formData" method="post" action="debug.cfm">
     <cfoutput query="getAssignments">
        row = #currentRow#

        Assignment 
        <input type="text" name="UniqueRecordID_#currentRow#" value="#getAssignments.UniqueRecordID#">
        <input type="text" name="Assignment_#currentRow#" value="#getAssignments.Assignment#">
        <!--- note: this field should have a more descriptive name --->
        <select name="Name_#currentRow#">
            <option value="">Not Assigned</option>
            <cfloop query="getNames">
                <option value="#TheValueCol#" <cfif getNames.TheValueCol eq getAssignments.Baylor>selected="true"</cfif>>
                    #TheDisplayCol#
                </option>
            </cfloop>
        </select>
        <br>
    </cfoutput>

    <!--- store total number of rows --->
    <cfoutput>
       <input type="hidden" name="totalRows" value="#getAssignments.recordCount#">
    </cfoutput>
    <input type="submit" name="submit" value="Update">
</form> 

After the form is submitted, use form.totalRows to loop and extract the values. Inside the loop, do whatever you need to do with the values (store in database, display, etc...)

<cfif structKeyExists(FORM, "submit")>
    <cfparam name="form.totalRows" default="0">

    <cfloop from="1" to="#form.totalRows#" index="variables.row">
        <!--- get each set of values --->
            <cfset variables.ID = FORM["UniqueRecordID_"& variables.row]>
        <cfset variables.assignment = FORM["assignment_"& variables.row]>
        <cfset variables.name = FORM["Name_"& variables.row]>

        <!--- display changed values (insert them into a db, etc..) --->
        <cfoutput>
           row [#variables.row#] 
               id = #variables.id# 
               assignment = #variables.assignment# 
               name = #variables.name#<br>
        </cfoutput>
    </cfloop>
</cfif>