Solved

looping over form fields and then adding in DB

Posted on 2011-02-13
28
258 Views
Last Modified: 2012-05-11
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
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 8
  • 6
28 Comments
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34883087
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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883119
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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883142
Yu are only considering field (p) while leaving all other fields
why can i know

or what is yuor point of view
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34883167
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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34883173
<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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883181
so ur point is this

<cfif StructKeyExists(FORM,'produc_name#i#') AND FORM['produc_name#i#'] NEQ ''>
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883183
in this case they need to add the produc_name for this to work
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883194
so you are saying is for singlefield, i need to rewite the query again and again

is that so

0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34883233
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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883243
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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34883295
<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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883308
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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34883332
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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34883342
>> 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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883368
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
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34883377
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34883468
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34883471
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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883633
so which is ur correct verison last one or the above used one
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34883660

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

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34883672
Great, i will implemented it and get back to u asap
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34888344
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34888427

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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34888476
no i would like this to created as function just it will be portable if used somewhere else
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34888500
ok, just wrap the function tag around it and pass in the form fields as arguments
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34890276
Ok will update 2marrow on this

i will write the function & show here if something is wrong or right
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34990935
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 34991754

 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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL querys that gives me from one table into another. 2 52
What is this datetime? 1 33
Substring works but need to tweak it 14 35
Migrate SQL 2005 DB to SQL 2016 4 32
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question