If I have a form with any number of variables, is there a good way in coldfusion to eliminate the need to type the variable names in again for an insert or update query. For example, in the following, I have to type in the variable name strPersonName 3 times, can't I type it once somehow?:
<form> <input name="strPersonName" >... etc... </form>
... After submission ...
<cfquery >
Insert into sometable (strPersonName) VALUES ('#FORM.strPersonName#')
</cfquery>
Control+C and Control+V are your friends.
In all seriousness it sounds like you're trying to devise a way for cfloop to do much of your job for you. It sounds wonderful in theory, but in practice it comes out as a mess, as I'll demonstrate, and further, it can cause errors or open your code up to mischief.
For instance, if I have a form..
FirstName - Text Box
LastName - Text Box
State - Select box, 50 states.
I can do this on submission.
<cfquery>
<cfset icount = 0>
<cfset istring = "">
Insert into Persons(<cfloop list="#form.fieldnames#" index="i"><cfset icount = icount + 1>
<cfif not listfindnocase("comma,delimited,list,of,fields,you,dont,want,to,insert",i)>
<cfset istring = istring & "," & i>
</cfif>
</cfloop><cfset istring = ReReplace(istring,"(^,|,$)","","ALL")>#istring#)
<cfset icount = 0>
Values(<cfloop list="#istring#" index="ix"><cfset icount = icount + 1>#form[ix]#<cfif icount neq listlen(istring)>,</cfif>)
</cfquery>
I might list "btnGo" (if that was the name of my submit button) in the 'comma,delimited,list...' I mention above.
This just builds the rudimentary query (or close, I didn't test). There are a number of problems though.. it's a mess, it's garbage to read. You have to account for nixing the comma at the end of the string. You also have to account for different data types so you'd almost want a structure with corresponding key names so you could identify values as int or strings (for 'quot' purposes).
There are certainly cases where looping over a subset of field names has it's uses, but it's not to solely skip time on coding.
Further it appears you are not using <cfqueryparam>. You need to be. CFQueryParam is your primary defense against sql injection, and offers something in the way of built-in validation. It won't make this goal of yours any easier though, so don't go looking to it for that.
The trouble with primarily focusing on making things easier is that it seldom does. You end up having to prop up and poke and it's generally not worth it. If you want my advice: take a deep breath, eat lunch at your desk, and write your code :)
Now, none of that is to imply that there aren't many insanely great tools that help with tasks and speed things up. As matter of fact, that's a large part of the purpose of UDFs and custom tags.
And that's not to say that I've never used cf to generate cf code, or queries, but I copied that code into the source and didn't leave cf trying to assemble sql statements to this extent in production.
<cfoutput><Cfset form.fieldnames = "test,this,thing"><Cfset form.fieldnames = "test,this,thing"> <pre> <cfquery>
<cfset icount = 0>
<cfset istring = "">
Insert into Persons(<cfloop list="#form.fieldnames#" index="i"><cfset icount = icount + 1>
<cfif not listfindnocase("comma,delimited,list,of,fields,you,dont,want,to,insert",i)>
<cfset istring = istring & "," & i>
</cfif><cfset istring = ReReplace(istring,"(^,|,$)","","ALL")>
</cfloop>#istring#)
<cfset icount = 0>
Values(<cfloop list="#istring#" index="ix"><cfset icount = icount + 1><cfqueryparam cfsqltype="XXXXXXXX" value="##form.#ix###"><cfif icount neq listlen(istring)>,</cfif></cfloop>)
</cfquery></pre></cfoutput>
After the code is generated and copied into the source, you'd want to replace each occurrence of XXXXXXXX with the actual sql type. Read up on cfqueryparam for types (like cf_sql_integer and cf_sql_varchar).
But again, there are scenario where writing code to generate chunks of sql inserts is the most sensible route, but it's more refined, like looping over a list of subset field names..
As an example, if I had a table called Classrooms and they looked like this
Teacher | Student1 | Student2 | Student3 | ...StudentN... | Student10
And I had a form where the admin could delete students from the class by checking boxes for which students to delete, I might arrange something like
update students set
<cfloop list="#form.delstudents#" index="s">
Student#val(s)# = '',
</cfloop>
Teacher = <cfqueryparam...>
(I know that the above example would be much better as a relational database, I was just scratching my head for an example).
I wouldn't do it myself, but this approach might work. On your form page:
fields = "field1,field2,etc";
labels = "label1,label2,etc;
datatypes = "cf_sql_varchar,cf_sql_date,etc";
<cfform>
<cfloop from = "1" to = "#listlen(fields)#" index = "i">
<cfinput
name = "#ListGetAt(fields, i)#"
label = "#ListGetAt(labels, i)#">
</cfloop>
two hidden tags go here where you pass the fields and datatypes variables.
On your action page:
insert into yourtable
(#form.fields#)
values
(
<cfloop from = "1" to = "#Listlen(form.fields)#" index = "i">
<cfqueryparam
cfsqltype="#ListGetAt(form.datatypes, i)#"
value = "#form[ListGetAt(form.fields, i)]#">
Intentionally left out is putting in commas as necessary. You can figure that out.
</cfloop>
)
Note that this code is untested and may have syntax errors.
