5 Replies Latest reply on Dec 12, 2007 7:17 PM by tinu88

    Comparing and Replacing Delimited Lists

    NuckNuck Level 1
      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
        • 1. Re: Comparing and Replacing Delimited Lists
          Level 7
          "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.
          • 2. Re: Comparing and Replacing Delimited Lists
            NuckNuck Level 1
            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.
            • 3. Re: Comparing and Replacing Delimited Lists
              Level 7
              "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.
              • 4. Re: Comparing and Replacing Delimited Lists
                cf_dev2 Level 1
                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
                • 5. Re: Comparing and Replacing Delimited Lists
                  tinu88
                  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