HostMySite.Com is sponsoring this tutorial, please visit their site today!

Add One-To-Many (Parent/Child) Data From a Single Form by: Matt Robertson

[Download in PDF Format] [Download in FlashPaper Format]

How Do I Update One-To-Many-Record Forms?

We'll concentrate on the fundamentals of the problem at hand.  Stuff like validating form inputs, testing referers and whatnot are beyond the scope of this tutorial, although we do make use of cfqueryparam, which should be a mandatory part of all ColdFusion database operations. 

Lets begin:  You have a single form that is meant to update stored data (for example, a household, with the names of all of the household residents inside).  This data is stored in two tables:  The main address info table and a related child table that holds each resident in a unique record, like so:

Address	123 Main St.
City	Springfield
State	Illinois
Residents	Name Relationship Employed? Criminal Record? Homer Father Yes Yes Marge Mother No Yes Lisa Daughter No No Maggie Daughter No No Bart Son No Yes
Notes	Approach Homer with extreme caution when carrying donuts.  Watch Bart's hands at all times

We'll use two simple queries to pull this form's data:

<cfquery
   name="Addr"
   datasource="#request.myDSN#">
   SELECT 
      Addr.Address,
      Addr.City,
      Addr.StateProvince,
      Addr.Notes
   FROM Addr
   WHERE 
   Addr.ID=
   <cfqueryparam 
      cfsqltype="CF_SQL_NUMERIC" 
      value="#url.ID#">
</cfquery>
<cfquery
   name="ResList"
   datasource="#request.myDSN#">
   SELECT 
      Residents.ID,
      Residents.Name,
      Residents.Role,
      Residents.Employment,
      Residents.Criminal
   FROM Residents
   WHERE 
   Residents.ParentID=
   <cfqueryparam
      cfsqltype="CF_SQL_NUMERIC"
      value="#url.ID#">
</cfquery>

Next comes the form itself.  The first thing you need to do is plug in a hidden form field that tells us how many residents there are.  We'll need that later on our form processing page.  Note that we are passing the primary key of the parent record via the url here.  You could also do this via a hidden form variable, client variable or session variable:

<cfoutput>
<form
   action="myformprocessor.cfm?ID=#url.ID#"
   method="POST"> 
<input
   type="Hidden"
   name="ResCount"
   value="#ResList.RecordCount#">

Next come the form field entries for the address, city and state data.  These are placed as usual.  Nothing special here:

<input
   type="Text"
   name="Address"
   value="#Addr.Address#">
<input
   type="Text"
   name="City"
   value="#Addr.City#">
<input
   type="Text"
   name="StateProvince"
   value="#Addr.StateProvince#">
</cfoutput>

Now comes the first tricky part; outputting the multiple child records from the Residents table.  Only its not so tricky.  We'll need to output the results of the ResList query from inside of a cfquery output loop, and find a way to make the form field names unique to each row.  ColdFusion's CurrentRow value -- automatic returned with all query output -- gives us a unique -- and, much more importantly, consecutive -- number that we can use for this purpose.

<cfoutput
   query="ResList">
<p>Resident ## #ResList.CurrentRow#<br>
<input
   type="Hidden"
   name="ResID#ResList.CurrentRow#"
   value="#ResList.ID#">
<input
   type="Text"
   name="Name#ResList.CurrentRow#"
   value="#ResList.Name#"><br>
<input
   type="Text"
   name="Role#ResList.CurrentRow#"
   value="#ResList.Role#"><br>
<input
   type="Text"
   name="Employment#ResList.CurrentRow#"
   value="#ResList.Employment#"><br>
<input
   type="Text"
   name="Criminal#ResList.CurrentRow#"
   value="#ResList.Criminal#">
</p>
</cfoutput>
<p>
<input
   type="Submit"
   value="Submit Form">
</p>
</form>

OK, we're done with the input form.  Note the hidden ID field that is a part of each child record above.  We'll use that later to identify which resident record needs to get updated.   

Now its time to build our form processor, myformprocessor.cfm.  We have a block of form fields representing our child table contents, and they all have unique names.  The next tricky part will be to attach those names to fields, grouped properly as discrete records, during the form processing step.  And this time it is a little tricky, but not that tricky.  First on our form processor we'll update the parent record:

<!--- myformprocessor.cfm --->
<cfquery
   datasource="#request.myDSN#">
   UPDATE Addr
   SET 
   Addr.Address=
      <cfqueryparam
         cfsqltype="CF_SQL_VARCHAR"
         value="#form.Address#"
         null="#YesNoFormat(not Len(form.Address))#">,
   Addr.City=
      <cfqueryparam 
         cfsqltype="CF_SQL_VARCHAR" 
         value="#form.City#" 
         null="#YesNoFormat(not Len(form.City))#">,
   Addr.StateProvince=
      <cfqueryparam 
         cfsqltype="CF_SQL_VARCHAR" 
         value="#form.StateProvince#" 
         null="#YesNoFormat(not Len(form.StateProvince))#">,
   Addr.Notes=
      <cfqueryparam 
         cfsqltype="CF_SQL_LONGVARCHAR" 
         value="#form.Notes#" 
         null="#YesNoFormat(not Len(form.Notes))#">
   WHERE 
      Addr.ID=
      <cfqueryparam
         cfsqltype="CF_SQL_NUMERIC"
         value="#url.ID#">
</cfquery>

OK, so much for the easy part (of the tricky part).  Now we update the child records.  We'll need to run a loop, first of all. The hidden recordcount value we stored earlier will tell us how many iterations of that loop to perform.  At this point we see why it is so important to have consecutive numbers appended to the fieldnames: We're going to be using the loop index value to match up with the appended numbers.  An entirely separate process that fortunately for us matches up precisely with the one we laid down on the form itself.

<cfloop
   index="LoopCount"
   from=1
   to=#Val(form.ResCount)#>
   <cfset variables.ResID="form.ResID#Evaluate(LoopCount)#">
   <cfset variables.ResID="#Evaluate(variables.ResID)#">
   <cfset variables.ResName="form.Name#Evaluate(LoopCount)#">
   <cfset variables.ResName="#Evaluate(variables.ResName)#">
   <cfset variables.ResRole="form.Role#Evaluate(LoopCount)#">
   <cfset variables.ResRole="#Evaluate(variables.ResRole)#">
   <cfset variables.ResEmpl="form.Employment#Evaluate(LoopCount)#">
   <cfset variables.ResEmpl="#Evaluate(variables.ResEmpl)#">
   <cfset variables.ResCriminal="form.Criminal#Evaluate(LoopCount)#">
   <cfset variables.ResCriminal="#Evaluate(variables.ResCriminal)#"> 
   <cfquery 
      datasource="#request.myDSN#">
      UPDATE Residents
      SET 
      Residents.Name=
      <cfqueryparam 
         cfsqltype="CF_SQL_VARCHAR" 
         value="#variables.ResName#"
         null="#YesNoFormat(not Len(variables.ResName))#">,
      Residents.Role=
      <cfqueryparam
         cfsqltype="CF_SQL_VARCHAR"
         value="#variables.ResRole#"
         null="#YesNoFormat(not Len(variables.ResRole))#">,
      Residents.Employment=
      <cfqueryparam
         cfsqltype="CF_SQL_VARCHAR"
         value="#variables.ResEmpl#"
         null="#YesNoFormat(not Len(variables.ResEmpl))#">,
      Residents.Criminal=
      <cfqueryparam
         cfsqltype="CF_SQL_VARCHAR"
         value="#variables.ResCriminal#"
         null="#YesNoFormat(not Len(variables.ResCriminal))#">
      WHERE 
         Residents.ID=
         <cfqueryparam
            cfsqltype="CF_SQL_NUMERIC"
            value="#variables.ResID#">
   </cfquery>
</cfloop>
<!--- end of myprocessor.cfm --->

The first part of the loop above gives us a usable field name and value in two steps.  The first step creates a temp value, and puts the form field variable name into it.  The first value held by variables.ResID will thus be "form.ResID1" for the first record we pull.  Te second step is to simply evaluate the value we created on the previous line, which for the first iteration of our example will be "Evaluate (form.ResID1)".  If you get the idea of combining these two steps into a single one, be advised this won't work without more gymnastics than it takes to just run the thing in two steps as shown.

And that, as they say, is that.

HostMySite.Com is sponsoring this tutorial, please visit their site today!

Please rate this tutorial:

Post a new comment on this tutorial
post a new comment on this particular tutorial
Your Name:
Your Email:
Comment Title:
Comments:
Key Phrase: