Using jQuery serialize() send save form data (using ColdFusion 9)

Question

I'm creating a new form and I thought I'd take jQuery's serialize() out for a spin. This is a totally new form so I'm not locked into form field names yet (all greenfield work).

I have the choice of using either data set (watch the equal signs closely there - this output is not a typo) :

data=userId=99&firstName=John&lastName=Doe&interestName=2&interestName=8&sdf&interestName=5 or data=userId=99&firstName=John&lastName=Doe&interestName_1=2&interestName_2=8&sdf&interestName_3=5

My current solution isn't pretty and with the conditional logic will not be easy to maintain. Now that I've explained my looping, here is the fugly code:

<cfoutput>  
    <cfloop list="#url.data#" index="i" delimiters="&">
        <cfset key = listfirst(i,'=')>
        <cfset keyValue = listlast(i,'=')>
    <cfif key eq 'userId'>
        <cfset userId = keyValue>
    </cfif>
    <cfif key contains 'interestName_'>         
        <cfquery name="rsInsert" datasource="playground">
            insert into table (optionId,userId)
                values
                (#keyValue#,#userid#)
        </cfquery>
    </cfif>
</cfloop>
</cfoutput>

What are some options to making this more efficient?

This would be much easier to answer if it were just one question, the way this system is intended to work. — user400654
– user400654, Commented Apr 20, 2016 at 18:50
"This could easily generate well over 60 SQL updates so there has to be better approach" that's easy to fix, simply concatenate all the updates to a single call to <cfquery> — user400654
– user400654, Commented Apr 20, 2016 at 18:52
well... i don't think either of the two questions are particularly good ones (for this site). They're very... stylistic questions, borderline subjective/opinionated. Both ways of submitting the form field names are acceptable, and inserting into the database effectively was just a code design flaw that i outlined in previous comment. — user400654
– user400654, Commented Apr 20, 2016 at 18:53
I tried to avoid the opinionated aspect but in this case, anyway is better than the way j came up with. :/ — HPWD
– HPWD, Commented Apr 20, 2016 at 18:54

user400654 · Accepted Answer · 2016-04-20 19:13:51Z

1

You should always avoid performing a db operation on each iteration of a loop if at all possible in any programming language. In this case, you can move the loop to inside the <cfquery> and do a multi insert. You could also further reduce the size of the query depending on db support by moving the INSERT INTO (...) portion outside of the loop. This of course depends on multi-insert being allowed in your datasource settings.

<cfquery name="rsInsert" datasource="playground">
    <cfloop list="#url.data#" index="i" delimiters="&">
        <cfset key = listfirst(i,'=')>
        <cfset keyValue = listlast(i,'=')>
        <cfif key eq 'userId'>
            <cfset userId = keyValue>
        </cfif>
        <cfif key contains 'interestName_'>         
            insert into table (optionId,userId)
                values
                (#keyValue#,#userid#);
        </cfif>
    </cfloop>
</cfquery>

Don't forget to prevent sql injection.

answered Apr 20, 2016 at 19:13

user400654

95.1k16 gold badges168 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

HPWD Over a year ago

I'm using MSSQL for the DB. I didn't know I could loop inside a cfquery! Holy wow! Will need to investigate that further. Absolutely on the cfqueryparam - always. I'm being called into a meeting and will try this in about an hour or so and let you now how it goes. Thanks for sticking with my borderline subjective/opinionated question. :)

Leigh Over a year ago

didn't know I could loop inside a cfquery! When it comes to queries, CF's only role is to interpret any CF variables embedded in the sql and send the generated sql string off to the database for execution. As long as that sql is valid - according to your dbms - it can be just about anything you want.

Dan Bracuk Over a year ago

My own experience is that sometimes looping inside the query makes things run faster and sometimes it makes them run slower. Also, I try to minimize the amount of ColdFusion processing inside query tags because it makes the code harder to troubleshoot.

user400654 Over a year ago

I prefer to do this with cfscript rather than tag-based code.

HPWD Over a year ago

For anyone else who finds this later, be sure to wrap your cfquery statement with a cftransaction so you can roll back ALL the changes in the event an error occurs.

|

Collectives™ on Stack Overflow

Using jQuery serialize() send save form data (using ColdFusion 9)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related