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:

<cfquery name="old" datasource="***">
  SELECT field
  FROM tableName
</cfquery>
<cfset temp = ValueList(old.field)>
<cfset changed = false>
<cfloop From="1" To="#ListLen(temp)#" index="field">
  <cfif ListGetAt(temp, field) NEQ ListGetAt(form.chk,field)>
    <cfset changed = true>
  </cfif>
</cfloop>

You may want to add a length check first.

[Esmeralda]

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

<cfif #ID# NEQ #chkChanged#>
        Changed
<cfelse>
        Unchanged    
</cfif>
 

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.