Dynamic generated update form not updating

Get Started
1

I have a table called categories:


CREATE TABLE IF NOT EXISTS `categories` (
  `cat_id` smallint(2) NOT NULL auto_increment,
  `category_id` smallint(2) NOT NULL,
  `language_abbr` char(3) NOT NULL,
  `category_name` varchar(32) NOT NULL,
  `category_intro` text,
  PRIMARY KEY  (`cat_id`),
  KEY `category_id` (`category_id`),
  KEY `language_abbr` (`language_abbr`),
  KEY `category_name` (`category_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

For the update funtionality I use a form from which the form fields are generated dynamically because the number of fields are depending on how many languages are active on the website.


<cfform method="post" preservedata="true">
    <cfset rowCount = 0>
    <cfloop index="i" from="1" to="#Ceiling( getlanguages.recordCount )#" step="1">
    <cfset rowCount = rowCount + 1 >
		<div class="form_wrapper">
    	<cfinput type="text" name="cat_id" value="#getCategory.cat_id[i]#">
			<label class="label medium_label">Category #getlanguages.language_abbr[i]# <span>*</span></label>
		</div>
		<div class="form_wrapper">
			<div class="input_bg input_bg_medium">
				<cfinput type="text" name="category_name_#rowCount#" class="input input_medium" value="#getCategory.category_name[i]#">      
			</div>
		</div>
		<div class="form_wrapper">
			<label class="label medium_label">Category Intro #getlanguages.language_abbr[i]#</label>
		</div>
		<div class="form_wrapper">
 			<div class="text_bg">
      	<cftextarea richtext="true" toolbar="Basic" skin="silver" name="category_intro_#rowCount#" width="520" height="200"  >#getCategory.category_intro[i]#</cftextarea>   
    	</div>
    </div>    
    </cfloop>
    </cfif>          
		<div class="form_wrapper"> 
			<cfinput type="submit" name="submit_button" value="Submit" class="submit_button">
      <cfinput type="hidden" name="counter" value="#Ceiling( getlanguages.recordCount )#">        
		</div>                    
	</cfform>

By doing this I not only get the right number of form fields the category_names and intros are dynamically generated as well, which i need for the update query.
On the update page I have the following query:


    	<cfset listCount = 0 />
      <cfloop index="fileField" from="1" to="#form.counter#" step="1">
      <cfset listCount = listCount + 1 />
  		<cfset category_name = "Form.category_name_#listCount#">
			<cfset category_name = Evaluate( category_name )> 
  		<cfset category_intro = "Form.category_intro_#listCount#">
			<cfset category_intro = Evaluate( category_intro )>      
			<cfquery datasource="#Application.dsn#">
      	UPDATE
        			categories
        SET
							category_name =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( category_name )#" >
            ,	category_intro = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( category_intro )#" >
				WHERE
        			cat_id IN ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( FORM.cat_id )#" list="true" /> )
      </cfquery>  
			</cfloop>

and although this approach working fine when inserting new categories(obviously without the where clause) it doesn’t hold up in updating the right records. Instead of updating the appropriate records, it set the values of all records to the last one in the form loop?

Does anybody see what i’m doing wrong?

2

Okay I have it working taking a slightly different approach. Instead of using a Index loop to populate the form elements I used a <cfloop queury> with as addition in the form a hidden field holding the id’s
After that I used the index loop for the update:


			<cfloop index="id" list="#form.listofids#" delimiters=","> 
			<cfquery datasource="#Application.dsn#">
      	UPDATE
        			categories
        SET
							category_name =  '#Evaluate("form.category_name_#id#")#'
            ,	category_intro = '#Evaluate("form.category_intro_#id#")#'
				WHERE
        			cat_id IN ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( id )#" list="true" /> )
      </cfquery>  
			</cfloop>

So basically both the insert and update queries are working, except for a small thing in the insert, that I hadn’t noticed as yet. In the insert query not only the name and intro text for each language need to be inserted but the lang_abbr for each language as well. These values should come from another query:


			<cfquery name="getLanguages" datasource="#Application.dsn#">
				SELECT
  						language_abbr
				FROM
  						languages
        
  			WHERE
  						language_isActive = <cfqueryparam cfsqltype="cf_sql_integer" value="1" />
      </cfquery>

So I tried the insert the following way:


      <cfset listCount = 0 />
      <cfloop index="fileField" from="1" to="getLanguages.recordCount" step="1">
      <cfset listCount = listCount + 1 />
  		<cfset category_name = "Form.category_name_#listCount#">
			<cfset category_name = Evaluate( category_name )> 
  		<cfset category_intro = "Form.category_intro_#listCount#">
			<cfset category_intro = Evaluate( category_intro )>           
			<cfquery datasource="#Application.dsn#">
				INSERT INTO
  						categories
        		(
        			category_id
        		,	language_abbr
						,	category_name
            , category_intro
        		)
  			VALUES
  					(
  						<cfqueryparam cfsqltype="cf_sql_integer" value="#Trim( catID )#" />
  					,	<cfqueryparam cfsqltype="cf_sql_char" value="#Trim( getLanguages.language_abbr )#" >
						,	<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( category_name )#" >
            ,	<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( category_intro )#" >
      			)
			</cfquery>      
      </cfloop>

But that only insert the first language_abbr for every record. Is it some how possible to loop over the languages within the INSERT QUERY?

Thank you in advance!

3

Okay solved. Was a stupid mistake from my side! The insert for the language_abbr:


<cfqueryparam cfsqltype="cf_sql_char" value="#Trim( getLanguages.language_abbr )#" >

should have been:


<cfqueryparam cfsqltype="cf_sql_char" value="#Trim( getLanguages.language_abbr[fileField] )#" >