looping insert...To be continued

[Newsgroup_User]

Sorry, but the post was getting too long to follow so I thought I would
start it over and ask for help piece by piece so I can learn this:
I viewed the created html from Coldfusion and my list has unique input names
from the looping list (List1, List2, etc) and the values are unique due to
the recordset values of the listsid.

Ok, using cfdump var="#form#" I am getting nothing in the output.
If I dump var = "#email#", I get the correct email addresse twice.
I don't know how to dump the values of the lists because of the fact that
they are incrementing dynamically based on the loop. I should figure out
that to see if the numbers are correctly apprearing.

Here is my form:
<form action="" method="get" name="showMeMy">
<cfoutput>
cfset i = 0>
<cfloop query="optionList">
<cfset i = i+1>
<input name="list#i#" type="checkbox" id="list#i#"
value="#optionList.listid#">
#optionList.listName#
</cfloop>
input name="email" type="hidden" id="email" value="#URL.email#" />
input name="Update" type="submit" id="Update" value="Update Subscription
List">
</cfoutput>
</form>

[Dan_Bracuk]

A couple of general hints on loops.
If you want query values, you have to specify a row number. The syntax is
queryname.fieldname[rownumber]

If you want list elements, you need to use the ListGetAt function.

It's probably worth your while to read the cfml reference manual for the cfloop tag. If you don't have one, the internet does.

[Newsgroup_User]

Here is my form:
<form action="" method="get" name="showMeMy">

There are two types of forms, get and post. Depending on which type you
pick determines what scope ColdFusion puts the results into.

method="get" = URL scope, use <cfdump var="#URL#">
method="post" = FORM scope, use <cfdump var="#FORM#">

The form tag you are showing in these messages has method="get", you
need to be using URL scope variables on your action page.

[Newsgroup_User]

Thanks. I forgot i changed that. When I dumped #Form#, i got:

struct
EMAIL wmkolcz@avemarialaw.edu
FIELDNAMES LIST1,LIST2,EMAIL,UPDATE
LIST1 9999
LIST2 1
UPDATE Update Subscription List


That is what I want to insert the value of the list elements (9999,1) and
the email address.

Seems the form is working fine. Now I have to figure out how to insert it
into the database, looping 1 list column and 1 email address per.

Here is what I had:
<cfif IsDefined('Update')>
<cfloop index="i" from="1" to="#optionList.Recordcount#">
<cfif evaluate("form.list#i#") NEQ "">
<cfquery name="optout" datasource="mailer">
INSERT INTO Optouttable (email, outoption)
VALUES ('#URL.email#', 'form.list#i#') <---I know that is wrong
</cfquery>
</cfif>
</cfloop>
<cflocation url="confirm.cfm" addtoken="no">
</cfif>

What should I look at next.

[Newsgroup_User]

<cfif evaluate("form.list#i#") NEQ ""> <!--- you do what you need here --->
<cfquery name="optout" datasource="mailer">
INSERT INTO Optouttable (email, outoption)
VALUES ('#URL.email#', 'form.list#i#') <--- do what you did in the cfif
statement #evaluate("form.list#i#")#--->
</cfquery>

[Newsgroup_User]

This is why I am confused. I am getting this error in the evaluate:
Variable form.list1 is undefined.

I have used the same syntax in a different looping insert and it worked
fine.

[Newsgroup_User]

Wally

Your next problem is going to be the age old HTML/HTTP issue of check
boxes not existing if they are not selected. This is just the way the
standard works, and the way you have your loop built it is going to
expect all the check boxes to be checked which I would not assume to
always be the case.

There are many solutions to this problem; <cfparam> all the check box
fields or check isDefined or structKeyExists before using any of the
value are two common ones.

The method I like to use is let form.fieldNames tell me what was passed.

To modify your example I would do something like this.

<cfloop list="#form.fieldnames#" index="field">
<!--- If the field is a list field --->
<cfif left(field,4) EQ "LIST")>
<cfquery ...>
INSERT INTO ...
VALUES ('#form.email#', #form[field]#
</cfquery>
</cfif>
</cfloop>

Look MA not EVALUATE.... :-)

[Newsgroup_User]

Ok, i am beginning to see. I am looking at the cfloop list in the docs.
So I need to replace the 'form.fieldname' with the actual field 'list'? How
can I replace that with the name of 'list' since it is incrementing from the
form?

Do I need to rename my fieldname in the form from list#i# to something
static?

[Newsgroup_User]

Ok, i am beginning to see. I am looking at the cfloop list in the docs.
So I need to replace the 'form.fieldname' with the actual field 'list'?
How can I replace that with the name of 'list' since it is incrementing
from the form?

Do I need to rename my fieldname in the form from list#i# to something
static?


NO, you just need to put all the pieces together.

Look at the <cfdump var="#form#"> you posted earlier.
struct
EMAIL wmkolcz@avemarialaw.edu
FIELDNAMES LIST1,LIST2,EMAIL,UPDATE
LIST1 9999
LIST2 1
UPDATE Update Subscription List


See the third line, there is a "field" named "FIELDNAMES" and it
contains a list of all the fields that where submitted by the form page.

<cfloop list="#form.fieldnames#" index="field">
This is going to loop over the list in the form.fieldnames key
[LIST1,LIST2,EMAIL,UPDATE] and put each list value into the variable
"field".

<cfif left(field,4) EQ "LIST">
This checks the value of the variable "field" in each loop to see if it
starts with the string "LIST" thus this is one of the list check box fields.

VALUES ('#form.email#', #form[field]#)
This inserts the values of form.email and the list field into the
database. In each loop itteration #form[field] is going to resolve to
form[LIST1] and form[LIST2] which in turn will resolve to the following SQL

VALUES ('#form.email#', #form[field]#

VALUES ('wmkolcz@avemarialaw.edu', 1)
VALUES ('wmkolcz@avemarialaw.edu', 9999)

[Newsgroup_User]

Now I get it. I had one small error that was causing a problem, but I fixed
that. And it works like a charm.

Thank you very very much for being patient with me and helping me. Now I
understand it.