Hey all,
I am working on a function that will take a set of data, and log it into a database. The data comes from another page, and is received in the form scope. Problem is, I never know which data will be coming, and it often sends data I don't care to log. So what I want to do is just use a CFUPDATE call to update the database, since that will take everything in the form scope and use it to update the database. Of course to do that, I need to remove keys from the form struct that don't have matching columns in the database.
I have a small function that "prototypes" the database (finds all columns, stores them in a list) and then another one that loops through all the keys in the form, and if it cannot find a match in list of column names, it removes them. Problem is, my cleaning function behaves oddly, leaving in keys that should have been removed, or when it removes a key, all the rest of the data seems to get shifted around, like up one position. I am starting to think my issues might due to some kind of variable scoping problem, because I have to pass this structure named form around like a million times (it comes in named form, it gets passed to my cleaning function which calls it form, it has to get passed to the CFupdate call named as form). Anyway, If anyone has had to do something like this, or can see any obvious problems in my code, please feel free to chime in, as I am kinda at a loss. Thanks.
<cffunction name="protoTypeDB" access="public">
<cfargument name="table" type="string" required="yes" hint="What is the name of the table we are validating against?">
<cfargument name="dsn" type="string" required="no" default="webserver" hint="What DB">
<cfset var returnstruct = structnew()>
<cftry>
<cfquery name="recordQuery" datasource="#arguments.dsn#" maxrows="1" cachedWithin="#CreateTimeSpan(1,0,0,0)#">
SELECT * FROM #arguments.table#
</cfquery>
<cfset returnstruct.success = true>
<cfset returnstruct.data = recordQuery.columnlist>
<cfcatch type="Any">
<cfset returnstruct.success = false>
<cfset returnstruct.error = cfcatch>
</cfcatch>
</cftry>
<cfreturn returnstruct>
</cffunction>
<cffunction name="StripNonDatabasefields" access="public" hint="Remove and fields from a scope that do not exist in the database">
<cfargument name="form" type="struct" required="yes" hint="A structure with fields to test existence for in the database">
<cfargument name="validationTable" required="yes" hint="What is the name of the table we need to validate against">
<cfargument name="dsn" required="no" hint="what DB" default="webserver">
<cfset var returnstruct = structnew()>
<cftry>
<!--- Create an array to hold all the names of fields that don't exist in our database that were passed in the form --->
<cfset NonRecordablefields = arraynew(1)>
<!--- Create a counter --->
<cfset counter = 0>
<cfset DataBaseFieldNames = protoTypeDB(arguments.validationtable,arguments.dsn).data>
<!--- Loop over all the fields in the form structure --->
<cfloop collection="#arguments.form#" item="Fieldname">
<cftry>
<!--- If a field exists in the form structure that does not exist in the list of columns returned by the query --->
<cfif ListFind(DataBaseFieldNames, fieldname) EQ 0 or len(arguments.form[fieldname]) EQ 0>
<cfset counter = counter +1>
<!--- Record the name of this form field in the nonrecordablefields array --->
<cfset NonRecordableFields[counter] = fieldname & " Is Empty or is not a valid database field and was not recorded. Value Was: #form[fieldname]#">
<!--- Strip the field from the form structure so it is not passed to the insert/update statments below --->
<cfset formIdDelete = StructDelete(arguments.form, fieldname)>
<cfelse>
<!--- Sometimes, for reasons unknown, Angel.com will send the same value twice, seperated by "," since we
only want the first instance of the string, we can use listfirst to get it --->
<cfset arguments.form[fieldname] = listfirst(arguments.form[fieldname],",")>
<cfset arguments.form[fieldname] = trim(arguments.form[fieldname])>
</cfif>
<cfcatch type="any">
<!--- Some field in the database does not exist in the structure i guess. No biggie. --->
</cfcatch>
</cftry>
</cfloop>
<cfset returnstruct.removedFields = NonRecordablefields>
<cfset returnstruct.form = arguments.form>
<cfset returnstruct.dbFieldNames = DataBaseFieldNames>
<cfcatch type="any">
<cfset returnstruct.error = cfcatch>
<cfset returnstruct.arguments = arguments>
<cfset returnstruct.form = arguments.form>
</cfcatch>
</cftry>
<cfreturn returnstruct>
</cffunction>
5
Replies
AdChoices