Insert records with coldfusion from dynamic input fields

I am using JavaScript to create some dynamic input fields. Each row/record has field names: "Auth[]" and "Name[]". The user can add as many as they want. After the form is submitted, below is what is submitted, along with many other input fields in the form.

I need to use ColdFusion to insert the records from the form/fields below.

I hope someone can assist. At the bottom of this question is the code I am using to build the form/javascript.

I already have an "ID" in memory, so I need to insert the auth/name records into a table like this:

ID | Auth    |   Name
5   | auth    |  Bob
5   | noauth | John
5   | auth     | Rebecca
5   | noauth  | gregory

This is what ColdFusion submits!!
Form Fields:
AUTH[]=auth,noauth,auth,noauth
FIELDNAMES=AUTH[],NAME[],SUBMIT1
NAME[]=Bob,John,Rebecca,Gregory
SUBMIT1=Submit Query

<script>
var ct = 1;
function new_link()
{
	ct++;
	var div1 = document.createElement('div');
	div1.id = ct;
	// link to delete extended form elements
	var delLink = '<div style="text-align:right;margin-right:65px"><a href="javascript:delIt('+ ct +')">Del</a></div>';
	div1.innerHTML = document.getElementById('newauth').innerHTML + delLink;
	document.getElementById('newlink').appendChild(div1);
}
// function to delete the newly added set of elements
function delIt(eleId)
{
	d = document;
	var ele = d.getElementById(eleId);
	var parentEle = d.getElementById('newlink');
	parentEle.removeChild(ele);
}
</script>

<style>
   #newlink {width:600px}
</style>
<form method="post" action="fieldsb.cfm">
<div id="newlink">
<div>
<table border=0>
	<tr>
		
		<td> Link URL: </td>
		<td> 
			<input type="checkbox" name="auth[]" value="auth">Authorized
			<br>
			<input type="checkbox" name="auth[]" value="noauth">Not Authorized
		</td>
	</tr>
	<tr>
		<td> Link Description: </td>
		<td>  <textarea name="name[]" cols="50" rows="5" ></textarea> </td>
	</tr>
</table>
</div>
</div>
	<p>
		<br>
		<input type="submit" name="submit1">
		<input type="reset" name="reset1">
	</p>
<p id="addnew">
	<a href="javascript:new_link()">Add New </a>
</p>
</form>
<!-- Template -->
<div id="newauth" style="display:none">
<div>
<table border=0>
	<tr>
		<td> Link URL: </td>
		<td> 
			<input type="checkbox" name="auth[]" value="auth">Authorized
			<br>
			<input type="checkbox" name="auth[]" value="noauth">Not Authorized
		</td>
	</tr>
	<tr>
		<td> Link Description: </td>
		<td> <textarea name="name[]" cols="50" rows="5" ></textarea> </td>
	</tr>
</table>
</div>
</div>

Open in new window

Can someone assist with the coldfusion code that I could put on the form action page? Thank you!
earwig75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
The convention for this is to have a number appended to the end of the name field for each row.   So, the field names would be name1, name2, name3... when your javascript adds a new row, would it be possible to create the new fields with the next sequence number appended?

If not, you may run into some trouble parsing the comma delimited list, what if someone puts a comma in their value?  If you can use javascript to clean out the field so no comma is entered, then you can use the List Functions to get the first, second, etc value from the list.

<cfloop index="row" from=1 to="#listLen(form.name)#">
    <cfset thisName= listGetAt(form.name,row)>
    <cfset thisAuth= listGetAt(form.auth,row)>

   now insert using thisName and thisAuth variables
</cfloop>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
earwig75Author Commented:
I can append a number to each field. If it isn't too much trouble, could you show the example for that loop as well?
0
_agx_Commented:
If you are using CF10, you might also try the new Application setting sameformfieldsasarray. Basically you enable the setting in your Application.cfc:

               this.sameformfieldsasarray=true;

And form fields with the same name will be submitted as arrays, instead of comma separated lists. Thus avoiding the issue of commas gdemaria mentioned.  Looping through the arrays should work for basic text fields, which always exist.  t would be problematic for checkboxes/radio buttons as they only exist when checked:

Disclaimer:  I haven't used this new feature much.  Prior to CF10, I always used the technique GD mentioned: group related fields by appending a numeric suffix fieldName1, fieldName2, etc...   If you are not using CF10+, it is a much more reliable technique than parsing lists.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

_agx_Commented:
Hm... on second thought, you should go with GD's approach after all.  The problem is that checkboxes only exist if they're checked.  So the arrays could be different sizes if one of the boxes was left unchecked, leading to a field mismatch.   Unless you can ensure one of the boxes is always checked,  GD's approach is more reliable.  Then you can use cfparam to set a default for when a related box is not checked.
0
earwig75Author Commented:
I edited the javascript to append a number at the end of each field.

Could you assist with the loop/query? Below is what I get when the form is submitted. Thanks again.

FIELDNAMES=NAME,AUTH,NAME2,AUTH2,NAME3,AUTH3,NAME4,AUTH4

Form Fields:
AUTH=auth
AUTH2=noauth
AUTH3=auth
AUTH4=noauth

NAME=Robert
NAME2=Greg
NAME3=John
NAME4=Rebecca

EDIT: I updated the form to use radio buttons. This is what I am using now:

<form method="post" action="actionpage.cfm">
<div class="input_fields_wrap">
    <button class="add_field_button">Add More Fields</button>
    <div><input type="text" name="name"> <input type="radio" name="auth" value="auth">Authorized <input type="radio" name="auth" value="noauth">Not Authorized </div>
</div>
<input type="submit" value="submit">
</form>
<script>
$(document).ready(function() {
    var max_fields      = 10; //maximum input boxes allowed
    var wrapper         = $(".input_fields_wrap"); //Fields wrapper
    var add_button      = $(".add_field_button"); //Add button ID
   
    var x = 1; //initlal text box count
    $(add_button).click(function(e){ //on add input button click
        e.preventDefault();
        if(x < max_fields){ //max input box allowed
            x++; //text box increment
          //  $(wrapper).append('<div><input type="text" name="name"/> <input type="radio" name="auth" value="auth">Authorized <input type="radio" name="auth" value="noauth">Not Authorized <a href="#" class="remove_field">Remove</a></div>'); //add input box
		$(wrapper).append('<div><input type="text" name="name' + x + '"/> <input type="radio" name="auth' + x + '" value="auth">Authorized <input type="radio" name="auth' + x  + '" value="noauth">Not Authorized <a href="#" class="remove_field">Remove</a></div>');
        }
    });
   
    $(wrapper).on("click",".remove_field", function(e){ //user click on remove text
        e.preventDefault(); $(this).parent('div').remove(); x--;
    })
});
</script>

Open in new window

0
_agx_Commented:
Don't forget to add a number to the first fields as well, ie:

AUTH1=auth
AUTH2=noauth
...
NAME1=xxx
NAME2=yyyy

In your form, store the total number of fields in single a hidden field ie form.totalNumOfGroups.  When the form is submitted, use that value to loop through the fields and extract the values
       <cfparam name="form.totalNumOfGroups" default="0">
	<cfloop from="1" to="#form.totalNumOfGroups#" index="counter">
                <!--- set default in case nothing was checked --->
                <cfparam name="FORM.Auth#counter#" default="noauth">

                <!--- extract values of form fields --->
                <cfset authValue = FORM["Auth"& counter]>
                <cfset nameValue = FORM["Name"& counter]>

               .... do something with values
       </cfloop>

Open in new window

0
earwig75Author Commented:
So, I am just inserting authValue and NameValue over and over again like this (using cfqueryparam of course)?

<cfquery...>
INSERT INTO table_name (authValue,nameValue)
VALUES (#AuthValue#, #NameValue#);
</cfquery>
0
gdemariaCommented:
Yes, that's correct, in agx's example,  he converts the numbered variable to a standard name so you can do the same insert every time using authValue and NameValue

Each iteration copies over the form field to the same variable name for easy use...
            <cfset authValue = FORM["Auth"& counter]>
             <cfset nameValue = FORM["Name"& counter]>
0
_agx_Commented:
(Edit: Didn't see you'd already mentioned cfqueryparam ;-)

Yep, just be sure to use cfqueryparam in the actual  queries.  In cases where you're executing the same sql multiple times, it boosts performance. Plus it helps protect against sql injection as well.
0
earwig75Author Commented:
I have an issue, because if a row is deleted and another added, the numbers out of of sequence.

I have a counter, and the counter is correct, but it may not always correspond to a field name. Below is an example.

Form Fields:
AUTH1=auth
AUTH2=noauth
AUTH4=noauth
AUTH5=auth
FIELDNAMES=TOTALFIELDS,NAME1,AUTH1,NAME2,AUTH2,NAME4,AUTH4,NAME5,AUTH5
NAME1=Robert
NAME2=John
NAME4=Henry
NAME5=Sally
TOTALFIELDS=4
0
_agx_Commented:
> TOTALFIELDS=4

Perhaps the name is a bit of misnomer. The counter should reflect the maximum field added, so in this case it should be 5.

EDIT: As far as gaps,  add a check on the action page to skip rows that are not defined. Example:

          <cfloop index="counter" .....>
                  <!--- if the textarea doesn't exists, assume the row was deleted and skip it .... --->
                  <cfif structKeyExists(FORM, "Name"&counter)>
                          .... extract variables, do query here ......
                  </cfif>
          </cfloop>

Open in new window

0
earwig75Author Commented:
right now it says how many fields were added... will that not work?
0
_agx_Commented:
NAME1=Robert
NAME2=John
NAME4=Henry
NAME5=Sally
TOTALFIELDS=4

No, not unless you renumber all the fields when a row is added or removed, which isn't worth it.

Right now the cfloop iterates from 1 to #TOTALFIELDS#, or in other words from 1 to 4.  So it would skip the last set of fields:  ie NAME5=Sally  AUTH5=xxx.  

Instead, just increment the total whenever a row is added.  Do not change it when a row is removed. Then skip any deleted rows on the action page.
0
earwig75Author Commented:
Can I use the counter to tell it how many times to loop?
0
_agx_Commented:
That's what the original example does already. It just uses different field names.

Say you're using Form.TOTALFIELDS = 5, this code would loop 5 times. If the submitted value of FORM.TOTALFIELDS  was 10, it would loop 10 times, etc...

        <cfparam name="form.TOTALFIELDS" default="0">
	<cfloop from="1" to="#form.TOTALFIELDS#" index="counter">

                 <!--- if field doesn't exists, assume the row was deleted and skip it .... --->
                  <cfif structKeyExists(FORM, "Name"&counter)>

                      <!--- set default in case nothing was checked --->
                      <cfparam name="FORM.Auth#counter#" default="noauth">
                      <!--- extract values of form fields --->
                     <cfset authValue = FORM["Auth"& counter]>
                     <cfset nameValue = FORM["Name"& counter]>

                      ... run insert query here ..... 

                  </cfif>
    </cfloop>

Open in new window

0
earwig75Author Commented:
I guess I just need to test this to understand. I am confused because the field names will be named like this... without "name3 or auth3"... the count will be 3. I thought you were saying that the last one would be skipped.

name1
auth1
name2
auth2
name4
auth4
0
_agx_Commented:
>  I thought you were saying that the last one would be skipped.

Yes.  In the earlier example. It could skip values because the code loops from 1 to 4, but your field names are: Name1,Name2,Name4,Name5 ...

Here's a simulated example:

<!--- simulate form fields --->
<cfset form.auth1 = "aa">
<cfset form.auth2 = "aa">
<cfset form.auth4 = "aa">
<cfset form.auth5 = "aa">
<cfset form.name1 = "aa">
<cfset form.name2 = "aa">
<cfset form.name4 = "aa">
<cfset form.name5 = "aa">
<cfset form.TOTALFIELDS = 4>



<cfparam name="form.TOTALFIELDS" default="0">
<cfoutput>
<cfloop from="1" to="#form.TOTALFIELDS#" index="counter">
      <!--- if field doesn't exists, assume the row was deleted and skip it .... --->
      <cfif structKeyExists(FORM, "Name"&counter)>
            <br>Processing: FORM.Auth#counter# and FORM.Name#counter#
      <cfelse>
            <br>Skipping deleted row #counter#
      </cfif>
</cfloop>
</cfoutput>

Open in new window


Results:

Processing: FORM.Auth1 and FORM.Name1
Processing: FORM.Auth2 and FORM.Name2
Skipping deleted row 3
Processing: FORM.Auth4 and FORM.Name4 

Open in new window


Notice the code never processes NAME5 and AUTH5?  As long as you don't decrement form.TOTALFIELDS when a row is deleted, the value will be correct ie in this case 5, and the code will work as expected.
1
earwig75Author Commented:
ok, I understand now, thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.