Comparing and Replacing Delimited Lists

[NuckNuck]

1. Database table contains a list of delimited values (ex. 1,2,3,4,5).
2. Form allows users to select items for deletion (ex. 2,3)

How does the database table get updated to reflect this? Ex. Table should now store values: 1,4,5

Thanks

[Newsgroup_User]

"How does the database table get updated to reflect this? Ex. Table
should now store values: 1,4,5"

<cfquery ....>
UPDATE aTable
SET aField = '1,4,5'
WHERE anID = aValue
</cfquery>

OR, most likely, normalize your database design so it does not have a
field with a list like this. 9 out of 10 times this type of data is
indicative of poor database design and this kind of difficulty is what
results.

[NuckNuck]

I agree with the database design, but this has already been prebuilt. I need to compare the original values that are stored in the table (ex. 1,2,3,4,5) and remove the values that passed in the form (ex.2,3). So the end result is the following list: 1,4,5

I tried looping through the table values and comparing these to the values passed in the form, but am having partial success.

[Newsgroup_User]

"I need to compare the original values that are stored in the table (ex.
1,2,3,4,5) and remove the values that passed in the form (ex.2,3). So
the end result is the following list: 1,4,5"

This would be done outside of the query logic. Just read in the two
lists and use list functions to do the comparison and modification.
Don't forget that a list is simple a string with delimiters and
sometimes this kind of functionality can be done more simply with basic
string comparison and manipulations. Also there are many enhanced list
functions on the <cflib> website.

To recap: investigate the ColdFusion and <cflib> list and string
functions and you should be able to cobble something together that will
meet your requirements.

[cf_dev2]

Since it appears you have no choice about the structure ...

Whatever you end up doing, keep in mind spaces may be signifigant when using list functions. The result of this would be zero due to the leading space in front of the number 4

<cfset yourList = "1,2,3, 4,5">
<cfset valueToFind = "4">
<cfoutput>#listFindNoCase(yourList, valueToFind)#</cfoutput>

And obviously a string comparison may produce different results than a straight comparison of numeric values. ie '5' does not equal '5.0'.

Good Luck

[Guest]

Depending on the application design (and database triggers and contraints not preventing this):

Delete first all entries and insert new entries. In a dual list gui, you typically only must take care for the one list of values which are still in (= selected). So you need only the values of the selected list box. This is what Skinner wrote.

Then you either to delete all/insert selected of if not normalized, update ... set selected = selected list. You don't have to compare what was there and what comes in. You just have to store the what comes in as selected.

THT,
Martin