10 Replies Latest reply on Jan 17, 2007 11:55 PM by Mr_Pappy

    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!
        • 1. Re: Inserting multiple records in one query from a loop
          azadisaryev Level 1
          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.
          • 2. Re: Inserting multiple records in one query from a loop
            azadisaryev Level 1
            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...
            • 3. Re: Inserting multiple records in one query from a loop
              Mr_Pappy Level 1
              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
              • 4. Re: Inserting multiple records in one query from a loop
                insuractive Level 3
                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.
                • 5. Re: Inserting multiple records in one query from a loop
                  azadisaryev Level 1
                  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.
                  • 6. Re: Inserting multiple records in one query from a loop
                    Dan Bracuk Level 5
                    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>
                    • 7. Re: Inserting multiple records in one query from a loop
                      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.
                      • 8. Re: Inserting multiple records in one query from a loop
                        Mr_Pappy Level 1
                        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!
                        • 9. Re: Inserting multiple records in one query from a loop
                          e{X}calibur Level 1
                          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?
                          • 10. Re: Inserting multiple records in one query from a loop
                            Mr_Pappy Level 1
                            Thanks. Worked perfect!