Looping through list and comparing to database field problem

[Esmeralda]

My situation:
I have a dynamic form with checkboxes. The checkboxes are submitted and added to a database. They are a list.

I want to compare the list with what is currently listed in the database. If there is anything new or removed, I want to know with a variable that says changed.

Basically:
Checkboxes = 1,2,3
Database rows and fields are:
row | field
1 | 2
2| 3

I want to know that 1 is new. OR if 2 is removed, I want to know that.

I've tried using listfind and listcontains, but I still cannot get this to work right.

I've even tried an array:

<cfoutput>#old.ID#</cfoutput>
<cfset chkChanged=ArrayNew(1)>
<cfoutput query="old">
	<cfloop list="#form.chk#" index="chk">
		<cfif #ID# IS NOT #chk# >
			<cfset chkChanged[CurrentRow]= id>
		</cfif>
	</cfloop>
</cfoutput>

[Esmeralda]

I guess I really want to know if there is a way to compare a list to a list, or a list to an array. Looping over just gives me the last value of the field. All I want to know is if anything has changed. Not exactly what has changed.

[Esmeralda]

As a temporary fix, I turned the array into a list. I then am checking the list length to see if there are any changes. The only problem is if they select and then de-select the same amount.

<cfif IsDefined('form.chkChanged')>	
   <cfset ID=quotedvalueList(qry.ID)>
  <cfset chkChanged=#form.chkChanged#>
  <cfif #ListLen(ID)# NEQ #ListLen(chkChanged)#>
        changed
  </cfif>
<cfelseif not IsDefined('form.chkChanged') AND #ListLen(ID)# IS NOT 0>
	changed
</cfif>

*Not my exact code so there may be some errors throughout.

[acoder]

Try this:
[code=cfm]<cfquery name="old" datasource="*** ">
SELECT field
FROM tableName
</cfquery>
<cfset temp = ValueList(old.f ield)>
<cfset changed = false>
<cfloop From="1" To="#ListLen(te mp)#" index="field">
<cfif ListGetAt(temp, field) NEQ ListGetAt(form. chk,field)>
<cfset changed = true>
</cfif>
</cfloop>[/code]You may want to add a length check first.

[Esmeralda]

Thank you. I found that the below works great for my purpose.

[code=cfm]
<cfif #ID# NEQ #chkChanged#>
Changed
<cfelse>
Unchanged
</cfif>
[/code]
With most of everything else above. So I can basically compare to see if the lists are the exact same using "valuelist" to turn the query into a comma separated list without a loop.

[acoder]

Glad to hear that you got it working.