asked on

ColdFusion Insert multiple loop, list values

Hello Experts
I have a rather large Cold Fusion form where a customer can select multiple destionations and have the results added to a database. There can be dozens of destinations submited from the form by way of checkboxes. So far, so good. However, each destination that is selected is inserted into a new database row with the appropriate values inserted for each field. So if someone selects Montreal, the database is be updated to reflect like so... Field = value i.e.

BrandID = 123, Brand = MyClient, Destination = Montreal, City = Montreal, State = Quebec, Country = Canada, Continent = North America.
BrandID = 123, Brand = MyClient, Destination = Niagra Falls, City = Niagra Falls, State = New York, Country = USA, Continent = North America.
BrandID = 123, Brand = MyClient, Destination = Canada, City = , State = , Country = USA, Continent = North America.

I want to get the brand and destination information from 2 separate tables and insert the results into a 3rd table called AC_DestinationList_Test after deleting the previous brand entries.
I tried an "insert" query followed by an "update" query with limited success. The BrandID, Brand and Destination fields are always populated in new rows correctly, but depending on the code I try, I either get nothing else inserted or the entire comma delim list in each field.

Can someone please show me what I'm doing wrong?

<cfquery name="GetRecord" Datasource="DBSource">
    Select *
    FROM AC_Brand
    where BrandID = #Brandid# 
    </cfquery>
		
    <cfquery name="getDestinations" datasource="DBSource">
    Select Destination, City, State, Country, Continent, Region, DestinationArea
    from AC_DestinationList
	</cfquery>	

	
	<CFQUERY Name="DeleteElement" Datasource="DBSource">
 	Delete From AC_DestinationList_Test
	where BrandID=#BrandID#
	</CFQUERY>
	

	<CFLOOP INDEX="ListDestinations" list="#Destination#">
	<CFQUERY Name="List_Destination" DataSource="DBSource">
	INSERT INTO AC_DestinationList_Test (Brand, BrandID, Destination, City, State, Country, Continent, Region, DestinationArea) 
	VALUES ('#GetRecord.BrandName#', '#BrandID#', '#ListDestinations#', '#City#', '#State#', '#Country#', '#Continent#', '#Region#', '#DestinationArea#'
	)
	</CFQUERY></CFLOOP>
	
	
		<cfoutput>
#Destination#
		</cfoutput>

Open in new window

gdemaria

fetching the other columns should be inside your loop, you also need to scope your variables

I changed the name of your index="" variable because the index is only one item of the list, not the list, that seemed confusing

    <CFLOOP INDEX="oneDestination" list="#Destination#">
        <cfquery name="getDest" datasource="DBSource">
          Select Destination, City, State, Country, Continent, Region, DestinationArea
          from AC_DestinationList
          where deistnationID = #val(oneDestination)#
        </cfquery>      
        <CFQUERY Name="List_Destination" DataSource="DBSource">
          INSERT INTO AC_DestinationList_Test (Brand, BrandID, Destination, City, State, Country, Continent, Region, DestinationArea) 
          VALUES ('#GetRecord.BrandName#', '#BrandID#', '#oneDestination#', '#getDest.City#', '#getDest.State#', '#getDest.Country#', '#getDest.Continent#', '#getDest.getDest.Region#', '#getDest.DestinationArea#'
          )
        </CFQUERY>
    </CFLOOP>

Open in new window

_agx_

If I'm understanding the form correctly ... I would suggest a different approach. If all of the "destination" checkboxes the same name, the selected ID's will be submitted as a comma delimited list like:

form.destinationID = 2,15,6,16

You can then use a simple CROSS JOIN to do the insert, which is more efficient than looping.

INSERT INTO AC_DestinationList_Test
(
Brand, BrandID, Destination, City, State, Country, Continent, Region, DestinationArea
)
SELECT b.BrandName, b.BrandID,
d.DestinationID, d.City, d.State, d.Country, d.Continent, d.Region, d.DestinationArea
FROM AC_Brand b CROSS JOIN AC_DestinationList d
WHERE b.BrandID = <cfqueryparam value="#form.Brandid#" cfsqltype="cf_sql_integer">
AND d.destinationID IN ( <cfqueryparam value="#form.destination#" list="true" cfsqltype="cf_sql_integer"> )

But, the tables really should be normalized. Just store the brand and destination ID's, not the details like BrandName, City, State, etc... That information can be retreived with a simple JOIN. That way you eliminate unecessary duplication, improve performance etc...

_agx_

Sorry the EE rich text editor messed up the query sample. Here it is again formatted.

=========================
If I'm understanding the form correctly ... I would suggest a different approach. If all of the "destination" checkboxes the same name, the selected ID's will be submitted as a comma delimited list like:

form.destinationID = 2,15,6,16

You can then use a simple CROSS JOIN to do the insert, which is more efficient than looping. (See below). But, the tables really should be normalized. Just store the brand and destination ID's, not the details like BrandName, City, State, etc... That information can be retrieved with a simple JOIN. That way you eliminate unnecessary duplication, improve performance etc...

<!---
    Assuming AC_DestinationList has a unique Record ID
    Note: Make sure to use CROSS JOIN correctly to prevent unexpected results
--->
INSERT INTO AC_DestinationList_Test
(
        Brand, BrandID, Destination, City, State, Country, Continent, Region, DestinationArea
)
SELECT   b.BrandName, b.BrandID,
         d.DestinationID, d.City, d.State, d.Country, d.Continent, d.Region, d.DestinationArea
FROM     AC_Brand b CROSS JOIN AC_DestinationList d
WHERE    b.BrandID = <cfqueryparam value="#form.BrandID#" cfsqltype="cf_sql_integer">
AND      d.destinationID IN 
         ( 
         <cfqueryparam value="#form.destinationID#" list="true" cfsqltype="cf_sql_integer"> 
         )

Open in new window

gdemaria

I agree with agx, I was too focused on the code as it was written.

His approach is much better, I suggest using it..

Mmoconnorinteractive

ASKER

Thanks guys for the quick response. Sage, I was wondering... in the first code that wasn't formatted correctly, there are several more parameter values at the end of the query. The final formatted code only has one parameter. The reson I ask s because I am getting a syntax error

<cfqueryparam value="#form.DestID#" list="true" cfsqltype="cf_sql_integer">

any Ideas?

_agx_

Yes, the EE editor messed up the first one. Try the reformatted example. Adjust the cfsqltype to match your column types.

gdemaria

Check the spelling of the destination variable.
In the above posts, it is written three different ways...

form.destID, form.destination and form.destinationID

In your code, change them so they all match the form field's name

_agx_

> The final formatted code only has one parameter.

.... I missed that comment. No, the final example has _two_ parameters: #form.BrandID# and #form.destinationID#.

https://www.experts-exchange.com/questions/25590876/ColdFusion-Insert-multiple-loop-list-values.html?cid=1573&anchorAnswerId=29006467#a29006467

Assuming you correct the variable names to match your form (as mentioned), it should work fine.
....

WHERE b.BrandID = <cfqueryparam value="#form.BrandID#" cfsqltype="cf_sql_integer">
AND d.destinationID IN
(
<cfqueryparam value="#form.destinationID#" list="true" cfsqltype="cf_sql_integer">
)

Mmoconnorinteractive

ASKER

Thanks! I'll check it out and will let you know.

Mmoconnorinteractive

ASKER

YIKERS! I'm still getting a syntax error. no matter what I try. Do I need to have the "form." in the cfqueryparams? When it is ine there I get a "Element BRANDID is undefined in FORM." error. When I take it out I get a "Syntax error in FROM clause." The id fields are numeric but changing them to cf_sql_numeric didn't help. Here's the revised code:

INSERT INTO AC_DestinationList_Test
(
Brand, BrandID, DestID, Destination, City, State, Country, Continent, Region, DestinationArea
)
SELECT b.BrandName, b.BrandID,
d.DestID, d.Destination, d.City, d.State, d.Country, d.Continent, d.Region, d.DestinationArea
FROM AC_Brand b CROSS JOIN AC_DestinationList d
WHERE b.BrandID = <cfqueryparam value="#form.BrandID#" cfsqltype="cf_sql_integer">
AND d.DestID IN
(
<cfqueryparam value="#form.DestID#" list="true" cfsqltype="cf_sql_integer">
)

Thanks again

_agx_

> Do I need to have the "form." in the cfqueryparams?

No. It's recommended to scope variables, but not strictly required in most cases. Since I can't see your
form code I "guessed" at your form type. But you're obviously using something different. Just omit the
scope for now. (You can add the proper scope once you get it working)

> When I take it out I get a "Syntax error in FROM clause."

I tested it with MySQL and it works perfectly. What type of database are you using ? Most enterprise
db's support CROSS joins. Except maybe MS Access..?

_agx_

> Most enterprise db's support CROSS joins. Except maybe MS Access..?

... To clarify that comment, MS Access is a desktop db (not in the enterprise category).
So it's not generally recommended for web apps. But anyway, is that what you're using?

Mmoconnorinteractive

ASKER

yup MS Access. Does That mean I'm back to looping?

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

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

Start Free Trial

Mmoconnorinteractive

ASKER

AGX... YOU ARE THE MAN!
Worked like a charm! Thanks sooooooooo much!

_agx_

You're welcome :) For PAQ purposes, it could also be done in a single cfquery. But .. the sql's
a little more convoluted. Here's an example for reference ..

<cfquery name="AddRecords" Datasource="DBSource">
INSERT INTO AC_DestinationList_Test
(
   Brand, BrandID, DestID, Destination, City, State, Country, Continent, Region, DestinationArea
)
SELECT   
       ( SELECT BrandName  FROM AC_Brand WHERE BrandID = <cfqueryparam value="#BrandID#" cfsqltype="cf_sql_integer"> )
         , <cfqueryparam value="#BrandID#" cfsqltype="cf_sql_integer">
         , d.DestID, d.Destination, d.City, d.State, d.Country, d.Continent
         , d.Region, d.DestinationArea
FROM     AC_DestinationList d
WHERE    d.DestID IN  
         (  
         <cfqueryparam value="#DestID#" list="true" cfsqltype="cf_sql_integer">  
         )
</cfquery>

Open in new window