Problem Looping

[mushBug]

I am trying to create an array of structures using data collected from a form.
The form contains 5 sets of seven fields each set containing various
information for a different person. The fields in each set are named with a
corresponding prefix identifying that particular person's role (e.g., role_1,
role_2, etc.), and a corresponding suffix identifing what part of that person'd
data it contains (e.g., first name, middle name, last name, ID, etc.) so that a
set of fields for any given person would look like this:

roleX_ID
roleX_FName
roleX_LName
roleX_EMail
etc.

For the purpose of inserting this data into a database, I want to separate the
prefix from the suffix and creat a CF Data Structure for each set of fields so
that the keys of each structure are generically renamed such as:

ID
FName
LName
EMail
etc.

Then insert each of these structures into an Array ending up with an array of
data structures.

So far, I am able to creat one giant structure containng all of the form
fields with their field names (roleX_ID, etc.) as the keys and the field values
as the values in the structure.

From here I dump all of the keys of this structure into an Array using --
structKeyArray(aryPersonnel) -- and loop through that array looking for
particular elements (ID, EMail, etc.) to be contained in the key. If it
matches a condition, I set a new value in a new structure equal to the valuel
that matches the key I'm examining.

Nice plan, but unfortunatly it does not render my desired result. I end up
with an array that contains 35 different structures instead of an array
containing 5 structures each with the seven key/value pairs of information.

I believe the problem may be in the way I am looping through the array, since
the conditions I have set would be met more than once through each iteration of
the loop.

I'm almost out of ideas so any help would be greatly appreciated. I have
attached some code for review.

-- Thanks in advance

<!--- Initialize a new structure --->
<cfset aryPersonnel= StructNew()>

<!--- Loop through the form and extract field values into one big structure
--->

<cfloop collection="#form#" item="var">

<cfif var CONTAINS "ID1">
<cfset ID1Field = "Form." & var>
<cfparam name="#ID1Field#" default="">
<cfset ID1 = StructInsert(aryPersonnel, "#var#",
"#Evaluate(ID1Field)#")>
</cfif>

<cfif var CONTAINS "ID2">
<cfset ID2Field = "Form." & var>
<cfparam name="#ID2Field#" default="">
<cfset ID2 = StructInsert(aryPersonnel, "#var#",
"#Evaluate(ID2Field)#")>
</cfif>

<cfif var CONTAINS "FName">
<cfset FNameField = "Form." & var>
<cfparam name="#FNameField#" default="">
<cfset FName = StructInsert(aryPersonnel, "#var#",
"#Evaluate(FNameField)#")>
</cfif>

<cfif var CONTAINS "MName">
<cfset MNameField = "Form." & var>
<cfparam name="#MNameField#" default="">
<cfset MName = StructInsert(aryPersonnel, "#var#",
"#Evaluate(MNameField)#")>
</cfif>

<cfif var CONTAINS "LName">
<cfset LNameField = "Form." & var>
<cfparam name="#LNameField#" default="">
<cfset LName = StructInsert(aryPersonnel, "#var#",
"#Evaluate(LNameField)#")>
</cfif>

<cfif var CONTAINS "Phone">
<cfset PhoneField = "Form." & var>
<cfparam name="#PhoneField#" default="">
<cfset Phone = StructInsert(aryPersonnel, "#var#",
"#Evaluate(PhoneField)#")>
</cfif>

<cfif var CONTAINS "EMail">
<cfset EMailField = "Form." & var>
<cfparam name="#EMailField#" default="">
<cfset EMail = StructInsert(aryPersonnel, "#var#",
"#Evaluate(EMailField)#")>
</cfif>
<cfset "#var#" = Trim(Evaluate(var))> <!--- commonizer --->

</cfloop>

<!--- Collect all of the key of the above structure into an array --->
<cfset keys = structKeyArray(aryPersonnel)>

<cfoutput>
<!--- Initialize a new array for a new structure --->
<cfset aryPeople = ArrayNew(1)>

<!--- Loop through the array of key names and determine the value for each
key and insert it into an array of structures --->
<cfloop from="1" to="#ArrayLen(keys)#" index="i">

<!--- initialize a new structure --->
<cfset aryPeople[i] = StructNew()>

<cfif keys[i] CONTAINS "ID1">
<cfset aryPeople[i].ID1 = "#structFind(aryPersonnel, keys[i])#">
</cfif>

<cfif keys[i] CONTAINS "ID2">
<cfset aryPeople[i].ID2 = "#structFind(aryPersonnel, keys[i])#">
</cfif>

<cfif keys[i] CONTAINS "FName">
<cfset aryPeople[i].FName = "#structFind(aryPersonnel, keys[i])#">

</cfif>

<cfif keys[i] CONTAINS "MName">
<cfset aryPeople[i].MName = "#structFind(aryPersonnel, keys[i])#">

</cfif>

<cfif keys[i] CONTAINS "LName">
<cfset aryPeople[i].LName = "#structFind(aryPersonnel, keys[i])#">

</cfif>

<cfif keys[i] CONTAINS "Phone">
<cfset aryPeople[i].Phone = "#structFind(aryPersonnel, keys[i])#">

</cfif>

<cfif keys[i] CONTAINS "EMail">
<cfset aryPeople[i].EMail = "#structFind(aryPersonnel, keys[i])#">

</cfif>

</cfloop>
<!---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~ ---->
<!--- When the above code is dumped out, the end result is a array with 35 (7
fields X 5 people) indivual sturctures (one for each field value) --->
<!--- All of the values are correct and the key is correct except there is
only one key/value pair per structure (instead of 7) --->
<!--- What I am trying to achieve is an array with 5 structures each with 7
key/value pairs --->
<!---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~ ---->

[Dan Bracuk]

If you have 35 form variables that you want to insert into a database, why not
just do it? What do you need to create arrays, structures, or anything else at
all?

Originally posted by: mushBug
I am trying to create an array of structures using data collected from a form.
The form contains 5 sets of seven fields each set containing various
information for a different person. The fields in each set are named with a
corresponding prefix identifying that particular person's role (e.g., role_1,
role_2, etc.), and a corresponding suffix identifing what part of that person'd
data it contains (e.g., first name, middle name, last name, ID, etc.) so that a
set of fields for any given person would look like this:

roleX_ID
roleX_FName
roleX_LName
roleX_EMail
etc.

For the purpose of inserting this data into a database, I want to separate the
prefix from the suffix and creat a CF Data Structure for each set of fields so
that the keys of each structure are generically renamed such as:

ID
FName
LName
EMail
etc.

Then insert each of these structures into an Array ending up with an array of
data structures.

So far, I am able to creat one giant structure containng all of the form
fields with their field names (roleX_ID, etc.) as the keys and the field values
as the values in the structure.

From here I dump all of the keys of this structure into an Array using --
structKeyArray(aryPersonnel) -- and loop through that array looking for
particular elements (ID, EMail, etc.) to be contained in the key. If it
matches a condition, I set a new value in a new structure equal to the valuel
that matches the key I'm examining.

Nice plan, but unfortunatly it does not render my desired result. I end up
with an array that contains 35 different structures instead of an array
containing 5 structures each with the seven key/value pairs of information.

I believe the problem may be in the way I am looping through the array, since
the conditions I have set would be met more than once through each iteration of
the loop.

I'm almost out of ideas so any help would be greatly appreciated. I have
attached some code for review.

-- Thanks in advance

[mushBug]

That's a really great question, I probably should have clarified a bit more.
The reason I need them all in an array of data structures is so I can
manipulate how I enter them into the database. They aren't going to just be
placed into just one table. They will all need to be inserted into several
different tables and checks would have to be run to see if they are in the
table first before an insert is made. Having them in an array of structures
will enable me to run the appropriate checks and write an INSERT statement only
once per table by looping through the array and depositing the desired
information into the appropriate fields.

[Dan Bracuk]

You can do all that with the original form fields, well, I can anyway.

Originally posted by: mushBug
That's a really great question, I probably should have clarified a bit more.
The reason I need them all in an array of data structures is so I can
manipulate how I enter them into the database. They aren't going to just be
placed into just one table. They will all need to be inserted into several
different tables and checks would have to be run to see if they are in the
table first before an insert is made. Having them in an array of structures
will enable me to run the appropriate checks and write an INSERT statement only
once per table by looping through the array and depositing the desired
information into the appropriate fields.

[mushBug]

Not sure I quite understand, care to share?

[Dan Bracuk]

Do this for each table.

<query name ="q1">
select count(fieldname) records
from table1
where fieldname = #form.fieldname# <!--- you'll need quotes if field is char
--->
</query>

<cfif q1.records is 0>
insert the record
<cfelse>
do something else, update the record maybe
</cfif>

[mushBug]

Thanks for this!

This is very helpful. I have actually thought about something like earlier,
my caviot is that my form field names aren't named the same as the database
fields. But this gives me another idea that may work. I believe I could loop
through my already existing structure or even the form itself, using the same
type using the same type of query and if the form.fieldName name contains a
desired element do whatever.

Thanks much for your thoughts!

It may take a little refining but something like this:

<cfloop collection="#form#" item="var">
<cfif var CONTAINS "roleX">
<query name="q1">
INSERT INTO users (
roleX_ID,
roleX_FName,
roleX_LName
)
VALUES (
'#Form.roleX_ID#',
'#Form.roleX_FName#',
'#Form.roeX_LName#'
)
</query>
</cfif>
</cfloop>