[bennychaos]

Hello all,

First post here, found this site through this thread but the answers didn't quite cover what I'd like to ask, but looked extremely helpful, so here goes.

I have a "Settings" section of my site whereby users can save preferences for their favorite genres, There are 7 options with 3 radio boxes for each (Never Send, Like, Love). My problem from what I understand is when I loop the results in the form action page I'm not matching the results to the correct row in the DB (MySQL). So let me paste some code to explain better...

The join is just a seperate table to store the genre names, so this brings back the current users prefs.

<cfquery name="rsGetGenres" datasource="#Request.dsn#" username="#Request.dsnUsername#" password="#Request.dsnPassword#">
SELECT * FROM tbl_custpref LEFT OUTER JOIN tbl_genre ON tbl_custpref.genre_ID = tbl_genre.genre_ID
WHERE cst_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Client.CustomerID#" />
</cfquery>

Form: So this is the front end which loads with their saved settings, I've added the #listOfIds# through reading the thread mentioned above in an attempt to give me something to loop through on the action page.

<cfset variables.listOfIds = ValueList(rsGetGenres.genre_ID)>

<cfform action="/account/Settings-Process.cfm" id="Settings">

<cfloop query="rsGetGenres">
<cfoutput>

<input type="radio" id="never_#genre_ID#" name="option_#genre_ID#" value="0" <cfif #custLike# eq 0>checked</cfif> /><label for="never_#genre_ID#">Never Send</label>

<input type="radio" id="like_#genre_ID#" name="option_#genre_ID#" value="1" <cfif #custLike# eq 1>checked</cfif>  /><label for="like_#genre_ID#">Like</label>

<input type="radio" id="love_#genre_ID#" name="option_#genre_ID#" value="2" <cfif #custLike# eq 2>checked</cfif>  /><label for="love_#genre_ID#">Love</label>

</cfoutput>
</cfloop>

<cfoutput><input type="hidden" name="IDs" value="#listOfIds#"></cfoutput>

<input type="submit" name="save" value="Save" />

</cfform>

This is what I'm trying to achieve for an update to the db, enter a new status - Never(0) Like(1) Love(2) for each row based on the form data. custLike being the option they selected (0,1 or 2) and the unique genre_ID to make sure the correct row is updated.

UPDATE	tbl_custpref
SET	custLike = 0
WHERE genre_ID = 1 AND cst_ID = 'D6F76525-07-25-11'

Action Page. I'm still pretty new to Coldfusion so I'm sure this is a simple one! I've dumped out the form structure to make sure everythings coming through (). It looks like its passing the newly select options through fine from the radio buttons, but I can't figure out how to loop and match the to the correct row in the DB.

<cfdump var="#form#">

<cfif structKeyExists(form, "save")>
<cfloop item="field" collection="#form#" >
	<cfquery datasource="#Request.dsn#" username="#Request.dsnUsername#" password="#Request.dsnPassword#">
            UPDATE	tbl_custpref
            SET		custLike = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">
            WHERE cst_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Client.CustomerID#" /> 
            AND genre_ID = [i]????????? Not sure what to do here to update the correct row in the loop ???????????[/i] 
	</cfquery>
</cfloop> 
</cfif>

The type of errors I've had so far are
'Incorrect integer value: 'OPTION_1,OPTION_2,OPTION_3,OPTION_4,OPTION_5,OPTION_6,OPTION_7,IDS,SAVE' for column 'custLike' at row 1 ' where I assume its trying to cram all the data into 1 field and silly things like that. I'm really just not sure how to write this.

Any help would be greatly appreciated. If I've missed explaining anything please let me know, its usually the syntax that causes me the most problems even if I know what I want todo.

Ben

[Craig328]

Welcome to DIC, bennychaos! You are enjoying a common issue with processing of dynamically generated forms: how to identify and evaluate dynamically named form elements in a form process page. Luckily, the solution is relatively easy and for your case, easier than most because you've added one thing many forget or don't do.

So, the real challenge is looping over all your possible dynamic form elements and getting their ids (in your case, their genre_id value) and then their actual form value. For this, you'll use your form.ids value which many folks wouldn't think to include. Including this value makes your life much simpler. We'll be working with this code you included at the end of your post:

<cfdump var="#form#">

<cfif structKeyExists(form, "save")>
<cfloop item="field" collection="#form#" >
	<cfquery datasource="#Request.dsn#" username="#Request.dsnUsername#" password="#Request.dsnPassword#">
            UPDATE	tbl_custpref
            SET		custLike = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form[field]#">
            WHERE cst_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Client.CustomerID#" /> 
            AND genre_ID = [i]????????? Not sure what to do here to update the correct row in the loop ???????????[/i] 
	</cfquery>
</cfloop> 
</cfif>

What you want to do is loop over all possibly existing form elements, get the genre_id, determine the form element's value and then run your update query. Because you included every possible genre id with your form.IDs element, what you'll need to do is to do a CFLOOP over that element treating it as a list:

<cfloop list="#form.IDs#" delimiter="," index="x"></cfloop>

This immediately allows you to zero in on the genre_id value in each loop iteration by referencing the index value, in this case "x".

What you'll want to do now is to make sure a form element exists for that genre_id. Now even though you created a form element for each genre_id, it's always a good habit not to assume anything so you'll want to check for existence before working with it:

<cfif IsDefined("Form.option_#x#")>

That will tell you if you have a form element to work. If you do then it's easier to simply create a variable you can refer to that's equal to the dynamic form element. Now, you COULD use the Coldfusion function evaluate()...but that's not very intuitive to use and it can be messy. Instead, because CF treats the form scope (and pretty much all variables scopes) like a structure, you can use structure syntax to get to the variable value:

<cfset variables.formElementValue = Form["option_#x#"]

At this point, you should be able to finish off your process as you now have the genre_id ("x") and the value of the dynamic variable (variables.formElementValue) and your CustomerID from the client scope...which is all you need to do the update SQL.

Give that all a try and post back here with any problems you run into. For further info on how to avoid using evaluate() and to leverage struct nature of CF variable scopes, check out this article. Great explanation for how to ditch CF's evaluate function.

Good luck!

This post has been edited by Craig328: 14 November 2011 - 08:07 AM

[bennychaos]

Thanks very much for your reply Craig it was extremely helpful, have yourself some good karma...

So here's the form action code I'm using now, works well unless its a total fluke!

<cfloop list="#FORM.IDs#" delimiters="," index="x">
<cfif IsDefined("FORM.option_#x#")>
<cfset VARIABLES.formElementValue = Form["option_#x#"]>
	<cfquery datasource="#Request.dsn#" username="#Request.dsnUsername#" password="#Request.dsnPassword#">
            UPDATE	tbl_custpref
            SET		custLike = <cfqueryparam cfsqltype="cf_sql_interger" value="#VARIABLES.formElementValue#">
            WHERE cst_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#CLIENT.CustomerID#" /> 
            AND genre_ID = '#x#'
	</cfquery>
</cfif>    
</cfloop>

Cheers

[Craig328]

Looks tight. Two comments:

• I notice you're using the username and password attributes for your CFQUERY tag. Unless you're overriding the default settings for the datasource (and you could indeed be doing that), you probably don't need these.
  
• Using bind parameters (CFQUERYPARAM) for every database interaction is a good habit to get into. The "genre_ID = '#x#'" piece could use one. It'll work fine without it but given Sony's grief over SQL injection attacks on their network, bind parameters can save you a lot of headaches.

Glad you got where you wanted to go!

This post has been edited by Craig328: 15 November 2011 - 06:38 AM

[bennychaos]

Good to know, thanks!