Inserting multiple records in one query from a loop

[Mr_Pappy]

Hi

Im am seriously struggling with something here...


PROBLEM:
I want to insert multiple checkbox records into my db….
A simple insert statement only inserts the 1st record…

How do I insert all records?

Eg: 10 surnames need to be inserted at once….

Check my code…


PLEEEASE, im actually pleading for help!

[azadisaryev]

guess it's time to learn some new tricks, eh?
first of all, on your action page (where the UploadProjAdministrators query is run), add this line somewhere at the top of page:
<cfdump var="#form#">

this will show you the data passed to your action page from your form page.
now you should see there one or more variables called "admin_xxxx" where xxxx is your adminID's. you will also see a variable called "formfields" that has all form's field names as value.

you have not yet guessed what to do with it all, here's a hint: loop through formfileds (which is a comma-delimited list), if the fieldname mathches a pattern of 'admin_*' insert its value into db.

[azadisaryev]

oh, if you want to insert SURNAMES into your db, you better change the value="#AdminID#" to value="#lastname#" in your checkboxes... but inserting AdminID is a much better idea...

[Mr_Pappy]

hi

Thanks for the help...
But i cant see "Formfields" in my cfdump.

This is what i see..

ACTION YES
ADMINID 34
ADMIN_32 32
ADMIN_33 33
ADMIN_34 34
COMPANY Cuthberts
FIELDNAMES COMPANY,PROJ_NAME,PROJ_DESC,ADMIN_32,ADMIN_33,ADMIN_34,ACTION,ADMINID,SUBMIT
PROJ_DESC xcv
PROJ_NAME xcv
SUBMIT Upload Project

So im not entirely sure on how to loop through the formfields.
PHope you can help me with this one..

Regards
Delon

[insuractive]

I believe he meant FieldNames. You can loop over FieldNames and use regEx to match ADMIN_[0-9]+ (might want to make that case insensitive), then if there is a match perform your insert statement.

i.e.
<cfquery name="UploadProjAdministrators" datasource="#application.db#">
<cfloop list="#Form.FieldNames#" index="i">
<cfif ReFindNoCase("ADMIN_[0-9]+", i) gt 0>
INSERT INTO tblProjectAssign (adminID)
VALUES ( #FORM #)
</cfif>
</cfloop>
</cfquery>

Also recommend using <cfqueryparam> to speed up your query and prevent sql injection.

[azadisaryev]

sorry, my fault... it has been a very long day... (and it is not over yet...)

of course, Michael/insuractive is right - it is FieldNames, not FormFields.

[Dan_Bracuk]

Sometimes putting your loop inside your query is more efficient than putting your query inside the loop. There are some ways to do this that are db specific. This way is generic:

insert into yourtable
(your fields)
<cfloop>
select distinct your values
from some small table
<cfif loop not finished>
union
</cfif>
</cfloop>

[e_X_calibur]

If you are using ms sql, why not allow the database handle it? I am assuming that the admin ids are coming from a database to start with. Loop through the fieldnames like stated above, however put the values into a comma-delimited list. Then change your insert statement to:

INSERT INTO tblProjectAssign (adminID)
SELECT adminID FROM originalTable
WHERE originalTable.adminID IN (#adminID_List#)

This means you don't have to make multiple connections to do the insert.

[Mr_Pappy]

Hi there

It seems like e{X}caliburs method worked.
Thanks..
But i didnt even have to put it inside a loop.
All I have is

<cfquery name="UploadProjAdministrators" datasource="#application.db#">
INSERT INTO tblProjectAssign (adminID)
SELECT adminID
FROM admin
WHERE admin.adminID IN (#somevar#)
</cfquery>

And it worked.

1 more question....
How do i insert another value into the db in the same query?

I have a field called projID which is also being passed in my code. ProjID also needs to be inserted alongside the #Somevar#.

Hope you can help!

[e_X_calibur]

Assuming that the projID is the same per record.

<cfquery name="UploadProjAdministrators" datasource="#application.db#">
INSERT INTO tblProjectAssign (adminID, projID)
SELECT adminID, #projID# AS projID
FROM admin
WHERE admin.adminID IN (#somevar#)
</cfquery>

I am of the belief that; if it can be done in the database, let the database do it. A database server has been optimized to handle this type of thing. So why not let it handle it?

[Mr_Pappy · Accepted solution]

Thanks. Worked perfect!