update conundrum

[Wants2learn]

Hey, I have a slight issue on my hands and don't know how to approach it.

Here's my situation: I have a form which allows the user to update
information based on a certain offer. On that page I display all the states
that the offer is allowed for. Now the user can "turn off" a state to not have
that offer active. I am using radiobuttons to display the list of all states.
The problem is when I try to change the state's preference I get an error
stating that the "state_id" is undefined. I know why I am getting that error,
I am looking for help to get around it.

If you look at my code you will see that I am using dynamic values for the
"name" attribute for the radiobutton.

================================================== ====================
CODE
<!--- selects the states and its isActive preference from the table --->
<cfquery name="getStates" datasource="#ds#">
select u.*, s.*, o.*
from updatestates u join states s on u.state_id = s.state_id join offers o
on u.offer_id = o.offer_id
where offer_id = #form.offer_id#
</cfquery>

<cfoutput query="getStates">
<!--- 'code' displays the states name onto the screen, such as NEW YORK or
CALIFORNIA or etc --->
#code#:
<!--- the 'name=' displays the numerical reference of the states from the
table --->
<input type="radio" name="#state_id#" value="1" <cfif isActive is
"1">checked</cfif>>YES |
<input type="radio" name="#state_id#" value="0" <cfif isActive is
"0">checked</cfif>>NO
</cfoutput>

<!--- UPDATE statement --->
<cfquery name="updateStates" datasource="#ds#">
update updatestates
set isActive = 0
where offer_id = #form.offer_id# and state_id = WHAT GOES HERE??
</cfquery>
================================================== ====================

So basically once processed my form looks like:
================================================== ====================
CODE
Alabama:
<input type="radio" name="1" value="1" checked> YES | <input type="radio"
name="1" value="0"> NO

Arizona:
<input type="radio" name="2" value="1" checked> YES | <input type="radio"
name="2" value="0"> NO

California:
<input type="radio" name="3" value="1"> YES | <input type="radio" name="3"
value="0" checked> NO

New York:
<input type="radio" name="4" value="1" checked> YES | <input type="radio"
name="4" value="0"> NO

Florida:
<input type="radio" name="5" value="1"> YES | <input type="radio" name="5"
value="0" checked> NO
================================================== ====================

You can see that the "name" has the dynamic value associated with it. So how
do I update NEW YORK from 1 to a 0 in my update statement??

Any reason why I can't use this:
================================================== ====================
CODE
<!--- UPDATE statement --->
<cfquery name="updateStates" datasource="#ds#">
update updatestates
set isActive = 0
where offer_id = #form.offer_id# and state_id = #form.state_id#
</cfquery>
================================================== ====================

So when the query is run it would look like:
update updatestates
set isActive = 0
where offer_id = 123 and state_id = 1

where it sets the 'isActive' column to 0 where offer_id is 123 and state_id is
1.

[The ScareCrow]

In the action page before the update statement, you will need to re do the
getStates query.

The loop through the result set, but this time checking the form field and
create 2 list
<cfset isActiveList = "">
<cfset notActiveList = "">
<cfoutput query="getStates">
<cfif IsDefined("form." & state_id)>
<cfif form[state_id] Is 1>
<cfset isActiveList = ListAppend(isActiveList, state_id)>
<cfelse>
<cfset isActiveList = ListAppend(notActiveList, state_id)>
</cfif>

Then you need 2 update queries, and qualify the lists as the state_id is text
<cfquery name="updateStates" datasource="#ds#">
update updatestates
set isActive = 0
where offer_id = #form.offer_id# and state_id = #ListQualify(notActiveList,
"'", ",", "All")#
</cfquery>
<cfquery name="updateStates" datasource="#ds#">
update updatestates
set isActive = 1
where offer_id = #form.offer_id# and state_id = #ListQualify(isActiveList,
"'", ",", "All")#
</cfquery>


Ken

[Wants2learn]

Ken,

Thanks for the help. You helped me out, I had to tweak the code a bit but the
end result, had it working right:

Well, as you all know my form calls the states dynamically and the results
look like:

================================================== ====================
CODE
Alabama:
<input type="radio" name="1" value="1" checked> YES |
<input type="radio" name="1" value="0"> NO

Arizona:
<input type="radio" name="2" value="1" checked> YES |
<input type="radio" name="2" value="0"> NO

California:
<input type="radio" name="3" value="1"> YES |
<input type="radio" name="3" value="0" checked> NO

New York:
<input type="radio" name="4" value="1" checked> YES |
<input type="radio" name="4" value="0"> NO

Florida:
<input type="radio" name="5" value="1"> YES |
<input type="radio" name="5" value="0" checked> NO
================================================== ====================
So I needed a way to determine what states need to get updated, and thus:
================================================== ====================
CODE
<!--- set a blank list for states on the active list and not on the active
list --->
<cfset isActiveList = "">
<cfset notActiveList = "">

<!--- re-query the table to pull back the info --->
<cfquery name="getStates" datasource="#ds#">
select u.*, s.*, o.*
from updatestates u join states s on u.state_id = s.state_id join offers o
on u.offer_id = o.offer_id
where offer_id = #form.offer_id#
</cfquery>

<!--- go the form and its states and update accordingly --->
<cfoutput query="getStates">
<cfif isdefined("form.fieldnames")>
<cfif form[state_id] is 1>
<cfset theState = #state_id#>
<cfset isActiveList = ListAppend(isActiveList, #state_id#)>

<cfquery name="updateStates" datasource="#default_ds#">
update showofferstates
set isActive = 1
where offer_id = #form.OfferID# and state_id = #theState#
</cfquery>
<cfelse>
<cfset theState = #state_id#>
<cfset notActiveList = ListAppend(notActiveList, #state_id#)>

<cfquery name="updateStates" datasource="#default_ds#">
update showofferstates
set isActive = 0
where offer_id = #form.OfferID# and state_id = #theState#
</cfquery>
</cfif>
</cfif>
</cfoutput>
================================================== ====================