Coldfusion Looping

Question

I need to use cfloop to insert records.

There's a form a customer fills out. They add an item from a dropdown list, and then enter in a quantity in a text box. Normally, the quantity is only between 1 and 5.

I need to run a loop to insert the records one at a time with a unique Item ID for each with a quantity of 1.

For example, if they add Specific Item 5 with a quantity of 4, I need 4 records inserted into the table with a qty of 1 each. What I really want more than the answer here is the thought process of how I'd come to that going forward without having to ask.

TRose · Answer 1 · 2017-07-05T00:15:16.000

1

That's... a weird way of recording things, but alright.

I am going to assume the form field's names are ITEM and QUANTITY. I am also assuming you are using either SQL or MySQL. The code should be nearly identical for either.

Here is what the code on your action page might look like:

<cfloop from="1" to="#FORM.QUANTITY#" index="i">
<cfquery name="insertOrder" datasource="DSN">
INSERT INTO TABLENAME
(
ITEMCOLUMN,
QTYCOLUMN
)
VALUES
(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.ITEM#">,
1
)
</cfquery>
</cfloop>

I added a cfqueryparam tag to declare data types and help prevent SQL injection. I don't know what your data types are in actuality, but you should definitely look into using it for any query that takes stuff from the FORM or URL scope.

edited Jul 05 '17 at 00:15

answered Jul 04 '17 at 23:37

TRose

1,718
1
16
32

2

So why don't you use `` in your example? You should also validate the input `FORM.QUANTITY` as this will thrown an exception if its content is not a castable numeric. I'm tempted to downvote. – Alex Jul 04 '17 at 23:52
When you're right, you're right. I didn't want to assume too much about data types and whatever, but half my answer is an assumption anyway. Might as well add a proper example. – TRose Jul 05 '17 at 00:15
Thanks @TRose .. your assumptions are correct and yea, I always SQL injection. If that's a weird way of doing it, any suggestions? :) – kevin freese Jul 05 '17 at 06:35
@kevinfreese my only question is why can you only count the quantity one at a time? You could bypass the need for a loop. – TRose Jul 05 '17 at 11:19
@TRose These are my instructions: If they Add a new piece of equipment do the following after they submit the form: 1 - Check to see if the item selected is serialized (by a query) 2 - Split logic based on if find item is serialized or not a. Serialized - Will create a loop to insert one record at a time with a quantity of 1 that will loop for the quantity specified on the form, but only save any serial number on 1st record b - Non-serialized - Will create one record with the quantity specified on the form - if enter a serial number for some reason, go ahead and save it – kevin freese Jul 06 '17 at 03:21

score 1 · Answer 2 · edited Jul 05 '17 at 19:51

Which flavor of SQL are you using? Rather than make a db connection and inserting once for each item, you may be able to do something like this:

<<<< sanitize FORM inputs >>>>

<cfquery name="insertRecords" datasource="#APPLICATION.DSN#">
    INSERT INTO <TheTable> ( ItemID, Qty, <OtherDetails> )
    VALUES 
        <cfloop from="1" to="#sanitizedFORMQuantity# index="i">
            ( 
                <cfqueryparam cfsqltype="cf_sql_integer" value="#sanitizedItemID#">
                , 1
                , <cfqueryparam cfsqltype="cf_sql_varchar" value="#sanitizedOtherDetails#"> 
            )  
            <cfif index NEQ sanitizedFORMQuantity>,</cfif> 
        </cfloop>
</cfquery>

This lets you build up the query and perform a single INSERT, making just one connection to the database. Even with just 5 items, it will be much faster.

NOTES:

ALWAYS sanitize FORM inputs (and any other data coming from any source other than you). queryparam primarily handles SQLi, but may allow other exploits to pass through.
Check that your cfsqltypes appropriately match up with the datatypes of the columns in your database. Know which ones work together to prevent unneeded background data conversions. https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-p-q/cfqueryparam.html
Try to minimize the number of times you need to connect to the database. Especially if your application and database reside on different servers (which they should).

score 0 · Answer 3 · answered Jul 05 '17 at 19:55

Really minor changes to Shawn's excellent answer

<cfquery name="insertRecords" datasource="#APPLICATION.DSN#">
DECLARE @OtherDetails varchar(80) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#sanitizedOtherDetails#"> 

INSERT INTO <TheTable> ( ItemID, Qty, <OtherDetails> )
VALUES 
    <cfloop from="1" to="#sanitizedFORMQuantity# index="i">
        ( 
            <cfqueryparam cfsqltype="cf_sql_smallint" value="#sanitizedItemID#">
            , 1
            , @otherDetails
        )  
        <cfif index NEQ sanitizedFORMQuantity>,</cfif> 
    </cfloop>
</cfquery>

This will create fewer temp variables
If you are really creating more than a small int number of variables, you have a real problem in the approach.

Coldfusion Looping

3 Answers3