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">
where BrandID = #Brandid#
<cfquery name="getDestinations" datasource="DBSource">
Select Destination, City, State, Country, Continent, Region, DestinationArea
<CFQUERY Name="DeleteElement" Datasource="DBSource">
Delete From AC_DestinationList_Test
<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#'