looping over form fields and then adding in DB

i have a four set of records which are same and i am doing a loop over 5 times over them

like

<cfloop index="w" from="1" to "5">
<input type="text" name="p#w#><br>
<input type="text" name="r#w#><br>
<input type="text" name="s#w#><br>
<input type="text" name="t#w#><br>
</cfloop>

Now they are looped 5 times. Now user comes in suppose he fills the value for the first one and leaves the remaining five, how can i insert the very first row if skips the other 4 and if he uses the 1st and 3rs then it should only insert two records

can you expert show me some idea

Database is MSSQL and Mysql both, i hope there will be a slight difference during a loop
LVL 16
Gurpreet Singh RandhawaWeb DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gdemariaConnect With a Mentor Commented:

 I put the function below, but have to warn you.   This is NOT something that you should put in a function.  

This code would only be called from a form submit, so normally you would only have ONE form that would use it.   If you have more than one form that would use it, then the save action would probably be different to match the other form.

If for some reason, the two forms pages were the same, then all you have to do is call the same action page, no need for a function.

So, the function does not make any sense, IMHO, you are just adding unnecessary code and complexity.

<cffunction name="doInsert" access="public" output="no" returntype="void">
    <cfargument name="p_form"      type="struct"  required="yes">
    <cfargument name="numbRecords" type="integer" required="yes">
    <cfargument name="dsn"         type="string"  required="yes">

    <cfset var requiredColumnList = "p,r"> <!---- list of columns that are needed to save the record ----->
    <cfset var data = arguments.p_form>    <!---- this is the form structure of all variables ----->
    <cfset var inputExists = true>
    <cfset var kk = "">
    <cfset var aCol = "">
        
     <cfloop index="kk" from="1" to="#arguments.numbRecords#">
       <cfset inputExists = true>
       <cfloop index="aCol" list="#requiredColumnList#">
          <cfif NOT structKeyExists(data, aCol & kk) or  len(data[aCol & kk]) eq 0>
             <cfset inputExists = false>
          </cfif>
       </cfloop>
       <cfif inputExists>
         <cfquery name="doInsert" datasource="#arguments.dsn#">
           insert into my table (
             p
            ,r
            ,s
            ,t
           ) values (
              '#data["p" & kk]#'
             ,'#data["r" & kk]#'
             ,'#data["s" & kk]#'
             ,'#data["t" & kk]#'
           ) 
         </cfquery>
       </cfif>
    </cfloop>
</cffunction> 


Call it with the form structure, then the number of rows to looop and the data source...
<cfset doInsert(form, 10, "myDsn")>

Open in new window

0
 
Brijesh ChauhanStaff IT EngineerCommented:
loop and check the existence of the input element when form is submited..

StructKeyExists(form,p#i#) -- Do insert...

upon form submission

<cfloop from="1" to="5" index="i">
   <cfif structKeyExists(FORM,'p#i#') >
          -- DO YOUR INSER --
    </cfif>
</cfloop>
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
the way you specified i know about it, but my question is will it insert the 1 and 4th row id specified

or there is some way using currentRow like stuff not sure abt that
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Yu are only considering field (p) while leaving all other fields
why can i know

or what is yuor point of view
0
 
Brijesh ChauhanStaff IT EngineerCommented:
You can just check for the existence of value.. this will ONLY insert the values what user have entered

<cfloop from="1" to="5" index="i">
    <cfif FORM['p#i#'] NEQ ''>
    	--- DO YOUR QUERY HERE ---    
    </cfif>
</cfloop>

Open in new window

0
 
Brijesh ChauhanStaff IT EngineerCommented:
<cfloop from="1" to="5" index="i">
    <cfif FORM['p#i#'] NEQ ''>
    	--- DO YOUR QUERY HERE ---    
    </cfif>
    <cfif FORM['r#i#'] NEQ ''>
    	--- DO YOUR QUERY HERE ---    
    </cfif>
    <cfif FORM['s#i#'] NEQ ''>
    	--- DO YOUR QUERY HERE ---    
    </cfif>
    <cfif FORM['t#i#'] NEQ ''>
    	--- DO YOUR QUERY HERE ---    
    </cfif>
</cfloop> 

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
so ur point is this

<cfif StructKeyExists(FORM,'produc_name#i#') AND FORM['produc_name#i#'] NEQ ''>
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
in this case they need to add the produc_name for this to work
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
so you are saying is for singlefield, i need to rewite the query again and again

is that so

0
 
Brijesh ChauhanStaff IT EngineerCommented:
Just checking  <cfif FORM['p#i#'] NEQ ''> would work ...

I usually create a function if it is the same query ..


<cffunction name="doInsert" access="public" output="no">
	<cfargument name="yorArgument" type="string" required="yes">
		--- YOUR QUERY HERE ---
</cffunction>

<cfloop from="1" to="5" index="i">
    <cfif FORM['p#i#'] NEQ ''>
    	<cfset doInsert(#FORM['p#i#']#)  /> 
    </cfif>
    <cfif FORM['r#i#'] NEQ ''>
    	<cfset doInsert(#FORM['r#i#']#)  />   
    </cfif>
    <cfif FORM['s#i#'] NEQ ''>
    	<cfset doInsert(#FORM['s#i#']#)  />    
    </cfif>
    <cfif FORM['t#i#'] NEQ ''>
    	<cfset doInsert(#FORM['t#i#']#)  />     
    </cfif>
</cfloop> 

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
the thing u are doing is something new to me as you are saying that you will create a function, can you explain a bit or write the queery complete , well my case is bit different as i am using this inside a function and i have no issues if i can use the same inside another function
0
 
Brijesh ChauhanStaff IT EngineerCommented:
<cffunction name="doInsert" access="public" output="no" returntype="void">
      <cfargument name="name" type="string" required="yes">
      <cfquery name="insertdata" datasource="#dsn#">
                   insert into yourtable() values()
                    where name = '#arguments.name#'
        </cfquery>
</cffunction>

<cfloop from="1" to="5" index="i">
    <cfif FORM['p#i#'] NEQ ''>
          <cfset doInsert(#FORM['p#i#']#)  />
    </cfif>
    <cfif FORM['r#i#'] NEQ ''>
          <cfset doInsert(#FORM['r#i#']#)  />  
    </cfif>
    <cfif FORM['s#i#'] NEQ ''>
          <cfset doInsert(#FORM['s#i#']#)  />    
    </cfif>
    <cfif FORM['t#i#'] NEQ ''>
          <cfset doInsert(#FORM['t#i#']#)  />    
    </cfif>
</cfloop>
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Dude, I still did not understood your work folio, u are passing a null value right in the insert statement, and suppose i move tis function move out and wwrite somewhere eelse, will this scenario work, if yes, how , can you please explain in detail

that will be nice of u
0
 
Brijesh ChauhanStaff IT EngineerCommented:
You can move the function anywhere you want to .. say in a cfc called test.cfc

Test.cfc

<cffunction name="doInsert" access="public" output="no" returntype="void">
      <cfargument name="name" type="string" required="yes">
      <cfquery name="insertdata" datasource="#dsn#">
                   insert into yourtable(a) values('#arguments.name#')
        </cfquery>
</cffunction>

Open in new window


Your main code

<cfset queryObj = createObject("component","test") />
<cfloop from="1" to="5" index="i">
    <cfif FORM['p#i#'] NEQ ''>
          <cfset queryObj.doInsert(#FORM['p#i#']#)  />
    </cfif>
    <cfif FORM['r#i#'] NEQ ''>
          <cfset queryObj.doInsert(#FORM['r#i#']#)  />  
    </cfif>
    <cfif FORM['s#i#'] NEQ ''>
          <cfset queryObj.doInsert(#FORM['s#i#']#)  />    
    </cfif>
    <cfif FORM['t#i#'] NEQ ''>
          <cfset queryObj.doInsert(#FORM['t#i#']#)  />    
    </cfif>
</cfloop>
0
 
Brijesh ChauhanStaff IT EngineerCommented:
>> Dude, I still did not understood your work folio, u are passing a null value right in the insert statement <<

Didn't get you on this,

1. Check if the FORM submit value is NOT NULL

<cfif FORM['p#i#'] NEQ ''>

2. IF it is NOT null then call the function which has the query to do the insert

<cfquery name="insertdata" datasource="#dsn#">
                   insert into yourtable(a) values('#arguments.name#')
</cfquery>

Does that make sense ?


0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
ok what it is doing, if any value is defined, it is going to insert right!

ok suppose i fill 1st field of row 1 and 3rd field of row 4, then it will gonna create 4 records or will do in the same one ewcord, the 1st and the 4th one
0
 
Brijesh ChauhanStaff IT EngineerCommented:
ok what it is doing, if any value is defined, it is going to insert right! -- YES, if the user inputs the value, it is going to call the insert function, otherwise it will NOT...

ok suppose i fill 1st field of row 1 and 3rd field of row 4, then it will gonna create 4 records or will do in the same one ewcord, the 1st and the 4th one   -- the rows that you filled are the ones which will be inserted.. as the ones which are not filled would be NULL..
0
 
gdemariaCommented:
myselfrandhawa,

 I assume p,t,r and S are COLUMNs in the row, is that right?  

 Question - do you need all 4 columns (p,t,r and S)  to be entered to create the record or will only one column be enough to create the record?  

 The follow example shows that IF ANY ONE field of p,t,r or s have a value, the record will be created.   If they are all empty, the record will not be inserted.

 You can enter a value in Row 1 column P and a record in Row 3 column S and the result will be two records created; the first with just a value in P and the second with a value in S.

 Is that what you want?

 If you want to UPDATE and DELETE as well, let me know, there are simple changes to make to allow for insert, update and delete.

-- note that you do not need to test if the field exists (unless one of the fields is a checkbox)
<cfloop from="1" to="5" index="i">
   <cfset inputExists = false>
   <cfif len(FORM["p#i#"]) + FORM["rp#i#"] + FORM["s#i#"] + FORM["t#i#"]>
      <cfset inputExists = true>
   </cfif>
   <cfif inputExists>
     <cfquery name="doInsert">
       insert into my table (
         p
        ,r
        ,s
        ,t
       ) values (
          '#form["p" & i]#'
         ,'#form["r" & i]#'
         ,'#form["s" & i]#'
         ,'#form["t" & i]#'
       )     
     </cfquery>
   </cfif>
</cfloop>

Open in new window

0
 
gdemariaCommented:
sorry, I forgot to use the len() on all form field in the CFIF.

here it is corrected...

<cfloop from="1" to="5" index="i">
   <cfset inputExists = false>
   <cfif len(FORM["p#i#"]) + len(FORM["rp#i#"]) + len(FORM["s#i#"]) + len(FORM["t#i#"])>
      <cfset inputExists = true>
   </cfif>
   <cfif inputExists>
     <cfquery name="doInsert">
       insert into my table (
         p
        ,r
        ,s
        ,t
       ) values (
          '#form["p" & i]#'
         ,'#form["r" & i]#'
         ,'#form["s" & i]#'
         ,'#form["t" & i]#'
       )     
     </cfquery>
   </cfif>
</cfloop>

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
so which is ur correct verison last one or the above used one
0
 
gdemariaCommented:

the last one, where it says "here it is corrected...."

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Great, i will implemented it and get back to u asap
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
That's great well what if the last comment of yours can be made as a function and then used, how that can be done, same as above or some changes neded
0
 
gdemariaCommented:

You can wrap the function tag around the code, no problem.

But why?   If you are already using all CFCs and functions, then you can make it a function.   But if you're not already usiing them, then why would you want to create one for this?

The reason you may want to make it a function  is that you will call the function from many different places in your code.  This seems like something you would only use in one place.  So no need to add the extra code..

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
no i would like this to created as function just it will be portable if used somewhere else
0
 
gdemariaCommented:
ok, just wrap the function tag around it and pass in the form fields as arguments
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Ok will update 2marrow on this

i will write the function & show here if something is wrong or right
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
in a hurry if u can wrap the functionality as custom function that will be nice, as i am leaving for the day or two might not be able to work on this later
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.