Coldfusion Loop for insert

Question

I have a question regarding coldfusion and loops. I have this program where I ask for user input from the user. The user can enter something for each food item.

<cfloop query = "GET_ITEM">
    <tr>
        <td align="left" nowrap>
           <label>#GET_ITEM.ITEM_NBR#</label>
       </td>
          <input type="hidden" name="Item_number" id="Item_number" 
                value="#GET_ITEM.ITEM_NBR#">
        <td>
            <input type="text" name="on_hand" id="on_hand" value="" size="20" 
                                    onKeyPress="javascript:CheckNumeric();" />
        </td>
        <td>
            <input type="text" name="transit" id="transit" value="" size="20" 
                   onKeyPress="javascript:CheckNumeric();" />
        </td>
        <td>
            <input type="text" name="target_level" id="target_level" value="" 
                   size="20" onKeyPress="javascript:CheckNumeric();" />
        </td>
        <td>
            <input type="text" name="percentonhand" id="percentonhand" value="" 
                   size="20" onKeyPress="javascript:CheckNumeric();" />
        </td>
    </tr>
</cfloop>

I want to insert each record into my table seperately using the below code.

<cfquery name = "insert_records"> 
  <cfloop index="Form.On_hand" list="#FORM.On_hand#" delimiters=","> 
    Insert into sometable
    (VENDORCODE,
      ITEM_NBR,
      Item_desc,
      Target_Level,
      Target_Date_Active,
      Target_Date_End,
      Vendor_name,
      Per_of_Actual
     )         
    Values (
      <cfqueryparam value = "#Form.Vendor_code#" cfsqltype = "CF_SQL_INTEGER">,     
      <cfqueryparam value = "#Item_number#" cfsqltype = "CF_SQL_VARCHAR"> ,
      <cfqueryparam value = "#Trim(itemdesc.Item_desc)#" cfsqltype = "CF_SQL_VARCHAR">,
      <cfqueryparam value = "#Trim(FORM.On_hand)#" cfsqltype = "CF_SQL_INTEGER">,
      '2014-12-02',
      '2040-01-01',  
      <cfqueryparam value = "#Trim(itemdesc.Vendor_name)#" cfsqltype = "CF_SQL_VARCHAR">,                
      100        
    )
   </cfloop>
</cfquery>

My issue is two things.

How do I ask for the user input and make each record unique?
After I get the input how do I insert each record seperately into the database.

By formatting your code, i notice that you have a section that is not within a `TD`. Not your issue, but it probably needs fixing. — crthompson, Dec 05 '14 at 16:19
@DanBracuk no but the loop inside the cfquery broke my mind. I gave them a +1 for wrecking me early on a Friday with nuanced conventions (it works... right? but I would never do it like this.) — Frank Tudor, Dec 05 '14 at 16:53
Frank, there are some cases where batching like this might be of some value - very large datasets inserted in a single connection. But overall I agree with you. I would loop outside the query and insert individually. — Mark A Kruger, Dec 05 '14 at 16:55
There is a time and a place for looping inside a query. This scenario might be an appropriate time, but I'm unsure about the placement of the loop. I don't work with mysql but I have vague recollections that it supports something resembling this syntax. insert into table (field1, field2) values (value1, value2), (value3, value4). That would insert two records. — Dan Bracuk, Dec 05 '14 at 17:04
Though with either method, be sure to wrap the entire batch of inserts in a transaction to ensure data integrity. — Leigh, Dec 05 '14 at 17:10
@user2967577 - What exactly is the desired result - in plain English (not code)? Judging from the answers below, there seem to be several different interpretations of your code. Providing an example would help clarify things. — Leigh, Dec 05 '14 at 17:19

score 1 · Answer 1 · answered Dec 05 '14 at 16:44

Issue one, how to make things unique, you have most of it down pat but if you do this:

<cfset x = 0>
<cfloop query="GET_ITEM">
  <cfset x++>
  <input name="uniqueID_#x#" value="#x#" type="hidden">
  <tr>
    <td align="left" nowrap>
       <label>#ITEM_NBR#</label>
    </td>
    <input type="hidden" name="Item_number" id="Item_number" 
            value="#GET_ITEM.ITEM_NBR#">
    <td>
        <input type="text" name="on_hand#x#" id="on_hand" value="" size="20" 
                                onKeyPress="javascript:CheckNumeric();" />
    </td>
   etc...
</cfloop>

you'll notice that you don't need to keep referring to the query name while inside your query loop when referencing the columns.

x at this point is essentially an index, by adding it to the form field name part you can reference each individual form.

So on receiving this entry, I would do something like this:

<cfquery name = "insert_records">
  <cfloop collection=#form# item="field">
    <cfif left(field,9) eq 'uniqueID_'>
      <cfset uniqueid = right(field,1)><!--- you'll have to work out your own logic for where you have more than 10 forms to a page--->
      Insert into sometable
        (VENDORCODE,
         ITEM_NBR,
         Item_desc,
         Target_Level,
         Target_Date_Active,
         Target_Date_End,
         Vendor_name,
         Per_of_Actual
        )         
       Values (
         <cfqueryparam value = "#Form.Vendor_code##uniqueid#" cfsqltype = "CF_SQL_INTEGER">,     
         <cfqueryparam value = "#Item_number#" cfsqltype = "CF_SQL_VARCHAR"> ,
         <cfqueryparam value = "#Trim(itemdesc.Item_desc)#" cfsqltype = "CF_SQL_VARCHAR">,
         <cfqueryparam value = "#Trim(FORM.On_hand)##uniqueid#" cfsqltype = "CF_SQL_INTEGER">,
         '2014-12-02',
         '2040-01-01',  
         <cfqueryparam value = "#Trim(itemdesc.Vendor_name)#" cfsqltype = "CF_SQL_VARCHAR">,                
         100        
        )
       </cfif>
     </cfloop>
   </cfquery>

There is something bothering me...It's the cfloop inside the cfquery (not your fault it was OP provided). So +1 for muscling through this. — Frank Tudor, Dec 05 '14 at 16:49
Personally I am not a fan of looping through the `FORM` collection in this scenario. When using a counter variable suffix, my preference is [this approach](http://stackoverflow.com/questions/15436110/iterate-through-a-dynamically-created-table-and-insert-data-into-sql-server-tabl/15436623#15436623). Basically store the max counter value in a hidden field. Then processing is a simple from/to loop. Also, side note, aside, there is no need to create a separate counter variable when using a query loop. Just use the `#currentRow#` variable :) — Leigh, Dec 05 '14 at 17:03
.. though to be honest, the OP is not very clear. At least not to me ;-) So it is hard to say whether the from/to loop is the correct approach here either. — Leigh, Dec 05 '14 at 17:06

Regular Jo · Answer 2 · 2014-12-05T21:44:36.120

1

You already have half the setup with the item_NBR field.

For all your other fields, name and id them as such

on_hand_#GET_ITEM.ITEM_NBR#
transit_#GET_ITEM.ITEM_NBR#

You will also want to change your item_nbr field like this

      <input type="hidden" name="Item_number" id="Item_number_#GET_ITEM.ITEM_NBR#" 
            value="#GET_ITEM.ITEM_NBR#">

Because, while ID's are for client-side (javascript), they must be unique to function properly.

Now in your query, you have

<cfloop list ="#GET_ITEM.ITEM_NBR#" index="iNbr">
<cfloop index="Form.On_hand" list="#form["on_hand_#iNbr#"]#" delimiters=","> 
 Insert into sometable
 (VENDORCODE,
   ITEM_NBR,
   Item_desc,
   Target_Level,
   Target_Date_Active,
   Target_Date_End,
   Vendor_name,
   Per_of_Actual
  )         
 Values (
   <cfqueryparam value = "#Form["Vendor_code_#iNbr#"]#" cfsqltype = "CF_SQL_INTEGER">,     
   <cfqueryparam value = "#iNbr#" cfsqltype = "CF_SQL_VARCHAR"> ,
   <cfqueryparam value = "#Trim(itemdesc.Item_desc)#" cfsqltype = "CF_SQL_VARCHAR">,
   <cfqueryparam value = "#Trim(form["On_hand_#iNbr#"])#" cfsqltype = "CF_SQL_INTEGER">,
   '2014-12-02',
   '2040-01-01',  
   <cfqueryparam value = "#Trim(itemdesc.Vendor_name)#" cfsqltype = "CF_SQL_VARCHAR">,                
  100        
 )
</cfloop>
</cfloop>

I would also change your index for the inner loop, to something like #iOH#. While you're code works, if you try to use #form.on_hand# later in your code, you will get the last value of the loop rather than the list.

As Leigh gracefully points out, you need to loop differently to handle a quantity like it appears you're trying to do.

Instead of <cfloop index="Form.On_hand" list="#form["on_hand_#iNbr#"]#" delimiters=",">

You probably want something like: <cfloop index="1" to="#form["on_hand_#iNbr#"]#" index="ioh">.

If you are indeed storing a quantity of items as 1 on each row.

As to itemdesc, this is likely a query? You will want to possibly include that withinn your outer loop (the one I added), if the data is different from row to row? If you do this, you will have to move your loops outside the <cfquery>

edited Dec 05 '14 at 21:44

answered Dec 05 '14 at 16:48

Regular Jo

5,190
3
25
47

Given the fact that the actual fields are going to have unique names, I see no need for the hidden item number field at all. Also, I see nothing that has it interacting with js. – Dan Bracuk Dec 05 '14 at 17:29
@DanBracuk You see nothing here interacting with js (by id name) and neither do I. We also don't see the whole document. The hidden item, that he already has in his document, makes iterating over the other fields extremely easy and easy to read on the code processing side. – Regular Jo Dec 05 '14 at 17:41
Valid point about Form.On_hand. But there is an inconsistency in your example. It uses both `Form.On_hand` and `form["On_hand_#iNbr#"]`. One of them is not going to exist ;-) – Leigh Dec 05 '14 at 21:17
@Leigh Thanks for the catch, fixed that. Just oversight. – Regular Jo Dec 05 '14 at 21:20
(Edit) Still not sure that make sense. What is the point of the list loop if the value is not even used? Also, now the index value is single number, so it is essentially looping from=1 to=1 ;-). Granted, the OP was not clear about actual versus expected results. [I said as much in the comments](http://stackoverflow.com/questions/27319940/coldfusion-loop-for-insert/27320778?noredirect=1#comment43101863_27319940). I think we need more information about what actually *should* be happening.. – Leigh Dec 05 '14 at 21:32
@Leigh Because the list of numbers also, with modifying the field names, as I demonstrated, gives him easy access to the field names, unless I've glossed something over and done something wrong. Mark's solution is very similar, but he creates an additional variable to do this, and Jarede does it a different way, splicing the number from the field name. Or did I make a mistake? – Regular Jo Dec 05 '14 at 21:36
@Leigh (about your edit, good catch, I will fix that, that should be a from/to loop since that seems like a quantity value. I copied from his code, but I should have caught that. – Regular Jo Dec 05 '14 at 21:40
1

@cfqueryparam - Yep, that is what I was getting at. Honestly I am not sure if the list loop was just the OP's mistaken attempt to insert a single record for each set of fields OR if they truly want to insert `x` records for each set, with `x` being the quantity on hand. – Leigh Dec 05 '14 at 21:44
@cfqueryparam the record is still on inserting one time. Something is not right with my loop. – user2967577 Dec 07 '14 at 00:22
@user2967577 Did you see where I said you probably want to change your loop to ``? – Regular Jo Dec 07 '14 at 05:30
Though they never clarified the goal - based on the accepted answer, it would seem they really want to insert a *single* record per item number, and the original list loop through `form.on_hand` was just a mistake. – Leigh Dec 08 '14 at 15:13

score 1 · Accepted Answer · answered Dec 05 '14 at 16:54

In your first loop try this:

<cfloop query = "GET_ITEM">
    <tr>
        <td align="left" nowrap>
           <label>#GET_ITEM.ITEM_NBR#</label>
       </td>
          <input type="hidden" name="Item_number" id="Item_number" 
                value="#GET_ITEM.ITEM_NBR#">
        <td>
            <input type="text" name="on_hand_#get_item.Item_nbr#" id="on_hand" value="" size="20" 
                                    onKeyPress="javascript:CheckNumeric();" />
        </td>
        <td>
            <input type="text" name="transit_#get_item.Item_nbr#" id="transit" value="" size="20" 
                   onKeyPress="javascript:CheckNumeric();" />
        </td>
        <td>
            <input type="text" name="target_level_#get_item.Item_nbr#" id="target_level" value="" 
                   size="20" onKeyPress="javascript:CheckNumeric();" />
        </td>
        <td>
            <input type="text" name="percentonhand_#get_item.Item_nbr#" id="percentonhand" value="" 
                   size="20" onKeyPress="javascript:CheckNumeric();" />
        </td>
    </tr>
</cfloop>

When submitted you will have a list of item numbers i form.item_nbr and cooresponding values for each number. You second loop can work like this:

<cfquery name = "insert_records"> 
  <cfloop index="#form.item_nbr#" index="item"> 
    Insert into sometable
    (VENDORCODE,
      ITEM_NBR,
      Item_desc,
      Target_Level,
      Target_Date_Active,
      Target_Date_End,
      Vendor_name,
      Per_of_Actual
     )         
    Values (
      <cfqueryparam value = "#Form.Vendor_code#" cfsqltype = "CF_SQL_INTEGER">,     
      <cfqueryparam value = "#Item#" cfsqltype = "CF_SQL_VARCHAR"> ,
       <cfqueryparam value = "#Trim(itemdesc.Item_desc)#" cfsqltype = "CF_SQL_VARCHAR">,
      <cfqueryparam value = "#Trim(FORM["on_hand_" & item)#" cfsqltype = "CF_SQL_INTEGER">,
      '2014-12-02',
      '2040-01-01',  
      <cfqueryparam value = "#Trim(itemdesc.Vendor_name)#" cfsqltype = "CF_SQL_VARCHAR">,                
      100        
    )
   </cfloop>
</cfquery>

I'm not sure exactly where the itemdesc.value is coming from in this query - I assume another query based on the item. In which case you may want to loop outside this query and do one insert query per item rather than batching them. There is not much of a penalty for that for a typical shopping cart form.

@user2967577 - As noted in the comments, be sure to wrap the above in a `cftransaction `to [ensure data integrity](https://en.wikipedia.org/wiki/Database_transaction). — Leigh, Dec 08 '14 at 15:15

Coldfusion Loop for insert

3 Answers3