[xheartonfire43x]

Yet again I am having a problem with form submission. I know exactly what is happening... I just don't know how to fix it.

form.cfm

<cfquery name="getCategories" datasource="#mydatasource#" username="#myusername#" password="#mypassword#">
  SELECT catID,CatTitle,SortOrder,Display,catPhoto FROM categories WHERE type = 'cat' AND display = 'Y'  ORDER BY sortOrder
</cfquery>

<cfset session.customvariable = getTickCount()>

<table cellpadding="0" cellspacing="5" border="0">
  <tr><th colspan="5">Category Manager</th></tr>
<cfform action="engine.cfm" method="post">
  <cfinput type="hidden" name="process" value="catManager">
  <cfinput type="hidden" name="customvariable" value="#session.customvariable#">
  <tr><th align="left" colspan="5">Active Categories</th></tr>
  <tr>
	<td>Category Title</td>
	<td align="center">Sort Order</td>
	<td align="center">Delete</td>
	<td colspan="2"><!--Column for Photo Manager Link and To Show Sub Categories--></td>
  </tr>
  <!---Begin Loop of Main Categories--->
  <cfloop query="getCategories">
	<cfinput type="hidden" name="catID" value="#getCategories.catID#">
	  <tr>
	  <td><cfoutput>#getCategories.catTitle#</cfoutput></td>
	  <td align="center">
		  <cfinput type="text" name="sortOrder" value="#getCategories.sortOrder#" validate="integer" validateAt="onsubmit" size="1" maxlength="3">
	  </td>
	  <td align="center">
		  <cfinput type="checkbox" name="display" value="N" checked="no">
	  </td>
	  <td>
		  <cfoutput><a href="catPhotomanager.cfm?catid=#getCategories.catID#" target="_blank">Photo Manager</a></cfoutput>
	  </td>
	  <td>Sub Categories</td>
	  </tr>
  </cfloop>
  <tr><td><cfinput type="submit" name="Update" value="Update"></td></tr>
</cfform>
  
</table>

engine.cfm

<cfcase value="catManager">
  <cfparam name="form.display" default="Y">
  <cfquery name="CatManager" datasource="#mydatasource#" username="#myusername#" password="#mypassword#">
	UPDATE categories SET
	  sortorder = <cfqueryparam value="#form.sortorder#" cfsqltype="cf_sql_integer">,
	  display = <cfqueryparam value="#form.display#">
	WHERE catid = <cfqueryparam value="#form.catID#" cfsqltype="cf_sql_integer">
  </cfquery>
  <cfdump var="#form#">
</cfcase>

Basically what is happening is that the loop produces however many inputs called CatID which is, in the engine, the where statement of the SQL. So basically if say the categories are Lunch, Dinner, Breakfast then there are 3 catIDs so the query ends up doing this

UPDATE categories SET
	  sortorder = <cfqueryparam value="#form.sortorder#" cfsqltype="cf_sql_integer">,
	  display = <cfqueryparam value="#form.display#">
	WHERE catid = 2,3,4

That obvisouly does not work right. Is there a way to fix it or do I just need to put an update button at the end of each row with multiple different forms?

[sansclue]

xheartonfire43x, on 16 Mar, 2009 - 10:49 AM, said:

That obvisouly does not work right. Is there a way to fix it or do I just need to put an update button at the end of each row with multiple different forms?

No. You have to update each record individually. Otherwise you are applying the same "sortorder" and "display" values to all of the categories (WHERE catid = 2,3,4)

You will either have to use separate forms, or put everything in a single form but give the fields unique names (like you did with the image options).