Solved

looping over form fields and then adding in DB

Posted on 2011-02-13
28
246 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
Question by:myselfrandhawa
  • 14
  • 8
  • 6
28 Comments
 
LVL 11

Expert Comment

by:Brijesh Chauhan
Comment Utility
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 15

Author Comment

by:myselfrandhawa
Comment Utility
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 15

Author Comment

by:myselfrandhawa
Comment Utility
Yu are only considering field (p) while leaving all other fields
why can i know

or what is yuor point of view
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
Comment Utility
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
Comment Utility
<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 15

Author Comment

by:myselfrandhawa
Comment Utility
so ur point is this

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

Author Comment

by:myselfrandhawa
Comment Utility
in this case they need to add the produc_name for this to work
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
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 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
<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 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 15

Author Comment

by:myselfrandhawa
Comment Utility
so which is ur correct verison last one or the above used one
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

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

0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
Great, i will implemented it and get back to u asap
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility

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 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
ok, just wrap the function tag around it and pass in the form fields as arguments
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
Ok will update 2marrow on this

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

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility

 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Set the max value for a column 7 32
Mysql not caching queries 4 45
Coldfusion RegEx 8 44
MYSQL simple update statement 3 32
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now