Monday, April 20, 2009

Multiple Records Insert (cfloop)

I was asked about this as a comment on a previous post on sortable lists. It used to be a question for me too so I thought I'd post it up top here.

Sometimes you need to take data in a form (or keep it in a table that is part or all form) that spans multiple records, so there are multiple 'groups' of values.

For example let us say we have these form fields: FirstName, LastName, Email. Normally, submitting these on a page is obvious. But what if we want to allow 5 of those? In fact what if the number is even adjustable by the user themselves (such as a javascript that lets the user add new form fields)?

Personally I use counter-loops and evaluate. I think there are more fluent ways to do this to be honest I just haven't had time to look them up and haven't had to do this for awhile. See end of the post for a note on doing the CFSET more properly on the post page.

Note: I'm ignoring most formatting, form validation, etc. in this example.

For our form, let's say we want to make 5 entries possible at a time. We'll do this:

<cfset recordQty = 5>

<form name="myForm" method="post" action="myPostScript.cfm">
<cfoutput><input type="hidden" name="recordQty" value="#variables.recordQty#"></cfoutput>

<cfloop from="1" to="#variables.recordQty#" index="ii">
<p>First <input type="text" name="FirstName#variables.ii#" size="30" maxlength="40" value="">
Last <input type="text" name="LastName#variables.ii#" size="30" maxlength="40" value="">
Email <input type="text" name="Email#variables.ii#" size="30" maxlength="45" value=""></p>

<input type="submit" name="addNames" value=" Add ">

Notice we appended the loop counter number to each field. So on the posting page we can just loop through with a counter again. (See the posts on ROTA as I think some version of this was in there too.)

Posting page, I make sure my recordQty field exists and is numeric and set it as a variable. You may want to do some if-exists logic on the numbered fields. I don't always.

<cfloop from="1" to="#variables.recordQty#" index="n">
<!--- first we set up field names to match what the form should be passing. --->
<cfset newfirst = "FirstName" & variables.n>
<cfset newlast = "LastName" & variables.n>
<cfset newemail = "Email" & variables.n>
<!--- then we get whatever values are in those field names and set them as vars --->
<cfset finalfirst = "#Evaluate(variables.newfirst)#">
<cfset finallast = "#Evaluate(variables.newlast)#">
<cfset finalemail = "#Evaluate(variables.newemail)#">
<!--- then we do something with this, maybe say IF a key field isn't blank before we get into SQL --->
[SQL insert or update here, or create list or array for one or more of those fields, etc.]

Quick and dirty that's it. Here's a note on the sets: Apparently it's better to use array notation when doing this, such as:

<cfset newfirst = variables["FirstName" & variables.counter]>

Haven't done it again since the ROTA to try it but that's a tip.