earwig75
asked on
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,no
FIELDNAMES=AUTH[],NAME[],S
NAME[]=Bob,John,Rebecca,Gr
SUBMIT1=Submit Query
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,no
FIELDNAMES=AUTH[],NAME[],S
NAME[]=Bob,John,Rebecca,Gr
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>ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
this.sameformfieldsasarray
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.
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.
ASKER
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
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:
Could you assist with the loop/query? Below is what I get when the form is submitted. Thanks again.
FIELDNAMES=NAME,AUTH,NAME2
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>
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
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>ASKER
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>
<cfquery...>
INSERT INTO table_name (authValue,nameValue)
VALUES (#AuthValue#, #NameValue#);
</cfquery>
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]>
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]>
(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.
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.
ASKER
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,NAM
NAME1=Robert
NAME2=John
NAME4=Henry
NAME5=Sally
TOTALFIELDS=4
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,NAM
NAME1=Robert
NAME2=John
NAME4=Henry
NAME5=Sally
TOTALFIELDS=4
> 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:
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>ASKER
right now it says how many fields were added... will that not work?
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.
ASKER
Can I use the counter to tell it how many times to loop?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
name1
auth1
name2
auth2
name4
auth4
> 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:
Results:
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.
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>Results:
Processing: FORM.Auth1 and FORM.Name1
Processing: FORM.Auth2 and FORM.Name2
Skipping deleted row 3
Processing: FORM.Auth4 and FORM.Name4 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.
ASKER
ok, I understand now, thank you!
ASKER