Inserting Multiple Rows into MySQL Database - r937 1

[3dColor]

I am having trouble trying to insert multiple rows into MySQL.

I did a lot of reading at different forums where Rudy (r937) posted answers, but I still can't get it to work.

My data is coming for a form box (FORM.cityCodes) where multiple items can be selected looks like this:
(dbv_pmId, FORM.cityCodes)
1170, 133
1170, 134
1170, 135

After reading Rudy's answers on other forums I replaced the VALUES statement with SELECT:

<CFQUERY datasource="#DSN#" >
INSERT INTO targetcs (pmFk, csFk)
SELECT (<cfqueryparam value=#dbv_pmId# cfsqltype='CF_SQL_INTEGER'>,
<cfqueryparam value=#FORM.cityCodes# cfsqltype='CF_SQL_INTEGER'>)
</CFQUERY>

pmFk is "property manager foreign key"
csFk is "city and state foreign key"

But changing it to SELECT didn't help.

Any ideas?

 

[DonQuichote]

Can you tell us what SQL results from that piece of XML?

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

what you want to end up passing to mysql looks like this:

INSERT INTO targetcs (pmFk, csFk) VALUES
(1170, 133)
,(1170, 134)
,(1170, 135)

to generate this, you will want to loop over your form fields with CFLOOP inside the VALUES clause of your CFQUERY

r937.com | rudy.ca

 

[3dColor]

Rudy,

I understand what you are getting at, but I can't seem to get the syntax right.

This is what I have tried that I think is the closes to being right:

<CFQUERY datasource="#DSN#" >
INSERT INTO targetcs (pmFk, csFk)
VALUES ( <cfloop index = "cityCodes" list = "#FORM.FeatureCodes#" delimiters="," >
<cfqueryparam value=#dbv_pmId# cfsqltype='CF_SQL_INTEGER'>,<cfqueryparam value=#cityCodes# cfsqltype='CF_SQL_INTEGER'>
</cfloop>)
</CFQUERY>

But I get the follow Coldfusion error:
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1170, 138 )' at line 8

 

[r937]

try like this --

<CFQUERY NAME="something" DATASOURCE="#DSN#" >
INSERT INTO targetcs (pmFk, csFk) VALUES 
 <CFSET comma = ''>
 <CFLOOP INDEX="cityCodes" LIST="#FORM.FeatureCodes#" DELIMITERS="," >
 #comma# ( <CFQUERYPARAM VALUE=#dbv_pmId# CFSQLTYPE='CF_SQL_INTEGER'>
         , <CFQUERYPARAM VALUE=#cityCodes# CFSQLTYPE='CF_SQL_INTEGER'> )
 <CFSET comma = ','>
 </CFLOOP>
</CFQUERY>

r937.com | rudy.ca

 

[3dColor]

Thanks Rudy!!!

You are the MySQL and Coldfusion expert!