cflooping list query

I'm trying to use a webform with input tags with the same name, when I submit the form all of the inputs with the same name get sent over as a list of comma separated values. I'm able to get a single fieldname to be inserted through a looping list query (see code) but when I try and update multiple fields with the same name it creates a record for each insert instead of combining them as single insert into the neccessary columns (database).

The example code is trying to take a--- room name | size | level ---form fields and insert them into a database table, each row should have the appropriate roomname, size, level inserted for each loop.

Being that each house (in this example) will have different names for each room contained for that particular house I'm trying to avoid creating a strict structure for the form fields as they will change for each house they are referencing.

Note that I can get the room name to be inserted but when I combine the other feilds they are not getting place on the same record. I hope this makes sense (see code for what works and what I have conceptually worked up.)
//THIS WORKS FOR JUST THE ROOM NAMES//////////////
<cfloop list="#form.roomname#" index="i">
     <cfquery name="roomname" datasource="#application.datasource#">
          insert into rooms
               (roomname)
          values
               ('#i#')
     </cfquery>
</cfloop>
 
////THIS INSERTS BUT NOT PROPERLY////////
<cfloop list="#form.roomname#" index="i">
     <cfquery name="roomname" datasource="#application.datasource#">
          insert into rooms
               (roomname)
          values
               ('#i#')
     </cfquery>
</cfloop>
<cfloop list="#form.size#" index="i">
     <cfquery name="size" datasource="#application.datasource#">
          insert into rooms
               (size)
          values
               ('#i#')
     </cfquery>
</cfloop> 
<cfloop list="#form.level#" index="i">
     <cfquery name="level" datasource="#application.datasource#">
          insert into rooms
               (level)
          values
               ('#i#')
     </cfquery>
</cfloop> 
 
///// CONCEPTUAL EVEN THOUGH IT'S WAY OFF, JUST TO HELP UNDERSTAND///
 
<cfloop list="#form.roomname#"&"#form.size#"&"#level#" index="i" index="a" index="b">
     <cfquery name="roomname" datasource="#application.datasource#">
          insert into rooms
               (roomname, size, level)
          values
               ('#i#','#a#','#b#')
     </cfquery>
     	</cfloop>

Open in new window

LVL 1
jasch2244Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

reitzenCommented:
I'm going to assume you have a normalized table structure.  So, you have a "house" table with a primary key, and you have a "rooms" table with a foreign key to the "house" table's primary key.

What I have done in the past is create unique field names, that are coded "row-wise" and then parsed them on the submit page.  For example
<!--- Create the form --->
<cfform...>
<cfloop from="1" to="6" index="i" step="1">
	<cfinput type="text" name="roomname_#i#" value=""/>
	<cfinput type="text" name="size_#i#" value=""/>
	<cfinput type="text" name="level_#i#" value=""/>
	<br/>
</cfloop>
 
 
	<cfinput type="hidden" name="houseID" value="9999"/>
	<cfinput type="submit" name="btnHouseFeatures" value="Save Changes"/>
</cfform>
 
<!--- The form has been submitted.  Loop through the form fields to submit values to the database for the selected houseID --->
<cfloop from="1" to="6" index="i" step="1">
	<cfset variables.sRoomname = FORM.roomname_#i#/>
	<cfset variables.iSize = FORM.size_#i#/>
	<cfset variables.iLevel = FORM.level_#i#/>
	<!--- Write the values to the database --->
	<cfquery name="roomname" datasource="#application.datasource#">
          insert into rooms
               (roomname, size, level)
          values
               ('#variables.sRoomname#','#variables.iSize#','#variables.iLevel#')
     </cfquery>
</cfloop>

Open in new window

0
jasch2244Author Commented:
Thank you for your suggestion. Yes, I do have the primary keys assigned as you have mentioned.

One thing that looks as a flag to me is the number you have assigned for the loop 1-6. What if  a house has 20 rooms and another has 10 how would this know? I'm a newb so I hope this is not a stupid question.  I have attached the form for simple purposes (just incase)

Question: what determines the number to loop through?
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
<cfform action="testRoomInsertAction.cfm" method="post" name="form1" format="html" id="form1">
<table width="452" border="0" cellspacing="2" cellpadding="2">
  <tr>
    <th width="144" scope="col"><div align="left">Room Name</div></th>
    <th width="144" scope="col"><div align="left">Dimensions</div></th>
    <th width="292" scope="col"><div align="left">Level</div></th>
  </tr>
  <tr>
    <td><cfinput type="text" name="roomname" id="room"></td>
    <td><cfinput type="text" name="size" id="dimensions"></td>
    <td><cfinput type="text" name="level" id="levels"></td>
  </tr>
  <tr>
    <td><cfinput type="text" name="roomname" id="room2"></td>
    <td><cfinput type="text" name="size" id="dimensions2"></td>
    <td><cfinput type="text" name="level" id="levels2"></td>
  </tr>
  <tr>
    <td><cfinput type="text" name="roomname" id="room3"></td>
    <td><cfinput type="text" name="size" id="dimensions3"></td>
    <td><cfinput type="text" name="level" id="levels3"></td>
  </tr>
  <tr>
    <td><cfinput type="text" name="roomname" id="room4"></td>
    <td><cfinput type="text" name="size" id="dimensions4"></td>
    <td><cfinput type="text" name="level" id="levels4"></td>
  </tr>
  <tr>
    <td><cfinput type="text" name="roomname" id="room5"></td>
    <td><cfinput type="text" name="size" id="dimensions5"></td>
    <td><cfinput type="text" name="level" id="levels5"></td>
  </tr>
  <tr>
    <td><cfinput type="text" name="roomname" id="room6"></td>
    <td><cfinput type="text" name="size" id="dimensions6"></td>
    <td><cfinput type="text" name="level" id="levels6"></td>
  </tr>
  <tr>
    <td><cfinput type="text" name="roomname" id="room7"></td>
    <td><cfinput type="text" name="size" id="dimensions7"></td>
    <td><cfinput type="text" name="level" id="levels7"></td>
  </tr>
  <tr>
    <td><input name="SubmitBtn" type="submit" id="SubmitBtn" value="Insert Contact"/></td>
    <td><div align="right"><strong>ListingID&gt;&gt;&gt;</strong></div></td>
    <td><cfinput type="text" name="listingID" id="listingID"></td>
  </tr>
</table>
</cfform>
</body>
</html>

Open in new window

0
reitzenCommented:
Not a stupid question at all.  The hardest part of web application development, sometimes, is interacting effectively with the user.

There are two ways you can approach this:
1. Give the user the option to tell you how many rooms this house may have.  Give them a form where they select the house, maybe, and also enter a number for the total number of rooms.  When they submit the filter, you take the value for the number of rooms and use that in your loop as the upper limit.

2.  Present the user with the form that has 10 rows.  Then, let the user know that 5 more rows will be added to the form each time they save their information.  This is beneficial in that the user saves often, lessing the chance of data loss, and it also relieves the user from having to count the number of rooms before they actually start inputting the information.

I have a page that uses the functionality described in #2 for phone numbers in our apartment communities.  Some communities have 4 phone numbers and some have 25+.  Let me know if you would like to see this page and I'll post it.

HTH
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

jasch2244Author Commented:
I'd love to see your code for the apartments.

I'm working on using your code mentioned above, I'll let you know shortly if I have any errors, theoretically it should work... Great idea for the number of rooms suggestion, as this is one of the columns in the listings (houses) directory. Thanks again! I'll let you know shortly.
0
jasch2244Author Commented:
reitzen: The code is very interesting! I would have never looped through input tags to create them on the fly (very nifty). However, I'm getting an error when I submit the form and it goes to the action page. I appears the #'s around the i (index variable) through it off. I have include the code I'm currently using.

Should I send it to an action page? Current process form page: roomDim_insert.cfm to room_action.cfm

Also, does the step variable come into play for just a basic script or did you use it for something on your site?

Thank you for your help
<!---roomDim_insert.cfm--->
<cfform action="room_action.cfm" name="myform" id="myform">
<cfloop from="1" to="6" index="i" step="1">
	<cfinput type="text" name="roomname_#i#" value=""/>
	<cfinput type="text" name="size_#i#" value=""/>
	<cfinput type="text" name="level_#i#" value=""/>
	<br/>
</cfloop>
	<cfinput type="hidden" name="listingID" value="6"/>
	<cfinput type="submit" name="btnHouseFeatures"/>
</cfform>
 
<!---room_action.cfm--->
 
<cfloop from="1" to="6" index="i" step="1">
	<cfset variables.sRoomname = FORM.roomname_#i#/>
	<cfset variables.iSize = FORM.size_#i#/>
	<cfset variables.iLevel = FORM.level_#i#/>
	<!--- Write the values to the database --->
	<cfquery name="roomname" datasource="#application.datasource#">
          insert into rooms
               (roomname, size, level, listingID)
          values
               ('#variables.sRoomname#','#variables.iSize#','#variables.iLevel#',#FORM.ListingID#)
     </cfquery>
</cfloop>

Open in new window

0
reitzenCommented:
I'm sorry, I neglected to include the #evaluate(FORM.fieldName)# function.  I have included the code from my "phone.cfm" page.  In it, I use stored procedures instead of <cfquery>, but you can assume what the queries do.

You'll also notice that I submit the form back to itself instead of submitting the form to a separate page. This is a personal preference.

The default for the step parameter is one.  I just include it out of habit.  You can leave it out if you prefer.

HTH
<cfparam name="propertyID" default="0" type="numeric"/>
<cfset variables.iPropertyID = propertyID/>
 
<cfif isDefined("FORM.propertyID")>
 
	<cfloop list="#FORM.FieldNames#" index="FormFields" delimiters=",">
		<cfif FormFields NEQ "btnSubmit"
		AND FormFields NEQ "propertyID"
		AND evaluate(FormFields) NEQ "0"		
		AND left(FormFields, find("_", FormFields, 1)-1) EQ "phoneTypeID"
		>
 
			<cfscript>
			variables.iPropertyPhoneID = right(FormFields, len(FormFields) - find("_", FormFields, 1));
			variables.iPhoneTypeID = Evaluate("FORM.phoneTypeID_#variables.iPropertyPhoneID#");
			variables.sPhoneNumber = numberOnly(Evaluate("FORM.phoneNumber_#variables.iPropertyPhoneID#"));
			variables.iPhoneDescription = trim(Evaluate("FORM.phoneDescription_#variables.iPropertyPhoneID#"));
			variables.iPhoneNotes = trim(Evaluate("FORM.phoneNotes_#variables.iPropertyPhoneID#"));
			variables.bDelete = iif(isDefined("FORM.delete_#variables.iPropertyPhoneID#"), DE("1"), DE("0"));
			if (find(variables.iPropertyPhoneID, "1,2,3", 1)) {
				variables.iPropertyPhoneID = 0;
			}
			</cfscript>
 
			<cfif variables.bDelete EQ 0>
				<cfstoredproc procedure="put_propertyPhone" datasource="#request.my_dsn#" returncode="no">
					<cfprocparam cfsqltype="cf_sql_integer" dbvarname="@propertyPhoneID" value="#variables.iPropertyPhoneID#" null="no"/>
					<cfprocparam cfsqltype="cf_sql_integer" dbvarname="@propertyID" value="#variables.iPropertyID#" null="no"/>
					<cfprocparam cfsqltype="cf_sql_integer" dbvarname="@phoneTypeID" value="#variables.iPhoneTypeID#" null="no"/>
					<cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@phoneNumber" value="#variables.sPhoneNumber#" null="no"/>
					<cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@phoneDescription" value="#variables.iPhoneDescription#" null="no"/>
					<cfprocparam cfsqltype="cf_sql_varchar" dbvarname="@phoneNotes" value="#variables.iPhoneNotes#" null="no"/>
				</cfstoredproc>
			<cfelse>
				<cfstoredproc procedure="del_propertyPhone" datasource="#request.dsn_bethany#" returncode="no">
					<cfprocparam cfsqltype="cf_sql_integer" dbvarname="@propertyPhoneID" value="#variables.iPropertyPhoneID#" null="no"/>
				</cfstoredproc>
			</cfif>
 
		</cfif>
	</cfloop>
 
	<p align="center"><strong>STATUS:</strong>&nbsp;&nbsp;&nbsp;Changes successfully saved.</p>
</cfif>
 
 
<cfstoredproc procedure="get_pickList" datasource="#request.my_dsn#" returncode="Yes">
	<cfprocresult name="get_phone"/>
	<cfprocparam cfsqltype="cf_sql_integer" null="yes"/>
	<cfprocparam cfsqltype="cf_sql_varchar" value="Phone" null="no"/>
</cfstoredproc>
 
<cfstoredproc procedure="get_propertyPhone" datasource="#request.my_dsn#" returncode="Yes">
	<cfprocresult name="get_propertyPhone"/>
	<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@propertyID" null="No" value="#variables.iPropertyID#"/>
	<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@phoneTypeID" null="yes"/>
</cfstoredproc>
 
<cfform type="html" action="phone.cfm" method="post">
<table align="left" width="98.5%">
<caption>Phone Numbers for <cfoutput>#get_propertyPhone.propertyName#</cfoutput></caption>
<tr>
	<th>Phone Type</th>
	<th>Phone Number</th>
	<th>Description</th>
	<th>Notes</th>
	<th>Delete?</th>
</tr>
<cfoutput query="get_propertyPhone">
<tr>
	<td><cfselect query="get_phone" name="phoneTypeID_#get_propertyPhone.propertyPhoneID#" selected="#get_propertyPhone.phoneTypeID#" display="listItem" value="pickListID"/></td>
	<td><cfinput name="phoneNumber_#get_propertyPhone.propertyPhoneID#" type="text" value="#phoneFormat(get_propertyPhone.phoneNumber)#"/></td>
	<td><cfinput name="phoneDescription_#get_propertyPhone.propertyPhoneID#" type="text" value="#get_propertyPhone.phoneDescription#"/></td>
	<td><cfinput name="phoneNotes_#get_propertyPhone.propertyPhoneID#" type="text" value="#get_propertyPhone.phoneNotes#"/></td>
	<td><cfinput name="delete_#get_propertyPhone.propertyPhoneID#" type="checkbox" value="1"/></td>
</tr>
</cfoutput>
 
<cfloop from="1" to="3" step="1" index="i">
<tr>
	<td><cfselect query="get_phone" name="phoneTypeID_#i#" display="listItem" value="pickListID" queryPosition="below"><option value="0">None</option></cfselect></td>
	<td><cfinput name="phoneNumber_#i#" type="text" value=""/></td>
	<td><cfinput name="phoneDescription_#i#" type="text" value=""/></td>
	<td><cfinput name="phoneNotes_#i#" type="text" value=""/></td>
	<td></td>
</tr>
</cfloop>
 
<tr>
	<td colspan="5" align="center" valign="bottom" height="60px"><br/>
		<cfinput type="submit" name="btnSubmit" value="Save Changes"/><br/><br/>
		<cfinput type="hidden" name="propertyID" value="#variables.iPropertyID#"/>
		<input type="button" onClick="window.close()" value="Close Window"/>
	</td>
</tr>
</table>
</cfform>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jasch2244Author Commented:
You da man! Thank you!!!!!!!!!!!!
It will take me a year to decipher your code. You really no your sh#$. Learned abunch of other stuff as well. Thank you for your generosity!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.