Help with Multiple Inserts 1

[farscp1]

I have an online system with many courses. I would like to insert a course_id for as many courses that are selected.

I'm using email address as the PK and the table that it should insert to will contain the following: h_email, course_id

The following is the insert loop I've set up in the action page:

<cfquery datasource="seligins">
INSERT INTO tblRegistration_details
(h_email, course_id)
VALUES
<cfloop index="i" to list="#Form.fieldnames#">
(#Form.h_email#, #Form.course_id#)
</cfloop>
</cfquery>

Can someone help me finish this code.

The way it should work is that if someone is registering for 3 courses those courses will be inserted as
test@mail.com, course001
test@mail.com, course201
test@mail.com, course003

Thanks in advance for any help.

 

[r937]

which dbms? sybase? informix? access? firebird? postgresql?

r937.com | rudy.ca

 

[farscp1]

SQL Server. Thanks for your reply.

 

[r937]

here's the best way to do it, using row constructors --

insert into tblRegistration_details
( h_email , course_id )
values
( 'joe@example.com' , 'course001' )
, ( 'joe@example.com' , 'course201' )
, ( 'joe@example.com' , 'course003' )

note that the above is only one statement

to generate this statement with coldfusion, you would have the CFLOOP inside the CFQUERY, in the VALUES clause, just like you had it, with a bit of tweaking to generate the commas

unfortunately, only mysql (maybe postgresql, not sure) supports this syntax

here's the way that you have to do it in microsoft sql server --

insert into tblRegistration_details
( h_email , course_id )
values
( 'joe@example.com' , 'course001' )
insert into tblRegistration_details
( h_email , course_id )
values
( 'joe@example.com' , 'course201' )
insert into tblRegistration_details
( h_email , course_id )
values
( 'joe@example.com' , 'course003' )

which is three statements, and i think odbc might have trouble passing more than one statement in a single connection, so this means you have to run three consecutive queries, so you would have the CFLOOP outside of the CFQUERY

finally, here's a way to do it with one statement in microsoft sql server --

insert into tblRegistration_details
( h_email , course_id )
select * from (
select 'joe@example.com' , 'course001' union all
select 'joe@example.com' , 'course201' union all
select 'joe@example.com' , 'course003' ) as dt

which is much better than three queries as far as database efficiency is concerned

note that the CFLOOP is back inside the CFQUERY again, but the code is just a wee bit trickier than the commas of the first example

r937.com | rudy.ca

 

[farscp1]

I did some more searching on the internet and ended up with the following code that works perfectly:

<cfset thisList = "#Form.course_id#">
<cfloop list = "#thisList#" index="nthisList">
<cfquery name="insertCourse" datasource="seligins">
INSERT INTO tblRegistration_details
(h_email, course_id)
VALUES
('#Form.h_email#', '#nthisList#')
</cfquery>
</cfloop>

for example if 3 courses are selected from the form, the action page will insert:

h_email course_id
------- ---------
test@mail.com course001
test@mail.com course002
test@mail.com course003

Thanks for trying to help. This forum is the best.

 

[r937]

thanks for almost thanking me

i explained that solution, i just didn't give you the cfml for it

r937.com | rudy.ca

 

[farscp1]

My bad, YOU did help me! LOL You get me thinking about this in different ways. To show my appreciation I officially thanked you using the tek-tips "Thank..." link.