asked on

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

reitzen

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

jasch2244

ASKER

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

reitzen

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

jasch2244

ASKER

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.

jasch2244

ASKER

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

ASKER CERTIFIED SOLUTION

reitzen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jasch2244

ASKER

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!