Skip navigation
Currently Being Moderated

Problem creating list of changed entries

Jun 8, 2012 9:47 AM

I've got a form that calls a list of file names and other details from a data table. There is a checkbox to select whether or not each item will appear on another page, and I'm trying to keep track of which records are changed each time the form is submitted. For example, if all items are selected (checked) and you uncheck the first 4, I'd like to create a variable that will save the ids of those items in a list so I can call it on a confirmation page. I'm having trouble getting my sql to populate the list properly though, and I'm not sure exactly why. In order to run the loop, I've collected a list of all the items displayed in a hidden form field called "listofIDs." That should be the starting point. What I've tried to do is as the loop is processed, compare the original value of the checkbox (displayOld) to the new value of the checkbox (displayNew) and if they are the same, remove that id from the original list. Then I could call that list as a url parameter and use it on the confirmation page. Unfortunately, it usually works properly when I go from all items selected to deselecting a couple, but once I've got some selected and some not at the starting point, it's not giving me the right IDs as the changed records. Could anyone please check my sql below and let me know what the issue is? I've included comments as to my thought process.

 

<cfif structKeyExists(form, "submit")>

 

<!--- set new list to equal original list --->

 

  <cfset changedIDs = form.listofIDs>

  <cfloop index="ID" list="#form.listofIDs#">

    <cfset currentID = "#ID#">

 

<!--- set displayOld as original value --->

 

    <cfset displayOld = "#file_listing.Display#">

 

<!--- set displayNew as current value --->

 

    <cfif IsDefined("form.display#ID#")>

      <cfset displayNew = "-1">

    <cfelse>

      <cfset displayNew = "0">

    </cfif>

 

<!--- compare old and new and if equal, find index of current id in changed ID list --->

 

    <cfif displayOld EQ displayNew>

      <cfset IDPosition = ListFind(changedIDs, currentID)>

 

<!--- delete ID at index determined above --->

 

      <cfset changedIDs = ListDeleteAt(changedIDs, IDPosition)>

    </cfif>

 

<!--- run the update query on all records (I've tried it on all and on only the changed records and it seems to work fine either way, so I believe the problem is with the list functions above) --->

 

    <cfquery datasource="ctband">

      UPDATE Files

      SET Display = #displayNew#

      WHERE ID = #currentID#

    </cfquery>

  </cfloop>

 

<!--- set the original and changed lists as url parameters to check if successful --->

 

  <cflocation url="/admin/filelist_update_confirm.cfm?startlist=#form.listofIDs#&en dlist=#changedIDs#">

</cfif>

 
Replies
  • Currently Being Moderated
    Jun 8, 2012 12:48 PM   in reply to jawmusic

    My first suggestion is to get rid of your query and cflocation code until you sort out your check boxes. 

     

    My second suggestion is, when in doubt, look at your data.  I generally troubleshoot if/else problems like this:

     

    if I see what I expect to see

    output yes

    else

    output no, what I expected, and what I got.

     

    Finally, once you solve your current problem of the user unchecking previously checked boxes, start looking at what happens if they check some previously unchecked boxes.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 8, 2012 10:56 PM   in reply to jawmusic

    I am not sure followed all that correctly ;-) But it sounds like you ultimately want to compare two lists of id's and identify which ones were removed from the originalList.

     

    ie

                  originalList = 1,3,4,5

                  newList = 3,5,6               

                  idsThatWereUnchecked = 1,4

     

    If so, it is simpler to give your checkboxes the same name (using the ID for the checkbox "value"). Then when the form is submitted, the checkbox value will be a list of all id's that were checked .  Simply compare that list to the original and find the differences. (There is a handy function at cflib.org called ListCompare which does exactly that). Then do whatever you need to do with the list of checked and unchecked ids.

     

                 <cfset idsThatWereUnchecked = ListCompare( form.originalList, form.nameOfCheckboxField )>

     

    Also, if you are updating all records with the same value you can a single IN (...) clause is generally more efficient than looping ie performing multiple updates.  At least for a moderate amount of values.

     

                  UPDATE   SomeTable

                  SET          Display  = 0

                  WHERE    ID IN ( <cfqueryparam value="#someListOfIDs#" cfsqltype="cf_sql_integer" list="true"> )

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 10, 2012 4:19 AM   in reply to jawmusic

    2 points regarding your original code.

     

    1) There is no need to introduce a new variable, currentID. ID is implicitly the current ID.

     

    2) Wasn't it your intention to do the cflocation outside the loop?

     

    I am thinking of a starting point like the following, then paying attention to what other posters have said:

     

    <cfif structKeyExists(form, "submit")>

        <!--- set new list to equal original list --->

        <cfset changedIDs = form.listofIDs>

        <cfloop index="ID" list="#changedIDs#">

            <!--- set displayOld as original value --->

            <cfset displayOld = "#file_listing.Display#">

            <!--- set displayNew as current value --->

            <cfif IsDefined("form.display#ID#")>

                <cfset displayNew = "-1">

            <cfelse>

                <cfset displayNew = "0">

            </cfif>

            <!--- compare old and new and if equal, find index of current id in changed ID list --->

            <cfif displayOld EQ displayNew>

                <cfset IDPosition = ListFind(changedIDs, ID)>

                <!--- delete ID at index determined above --->

                <cfif IDPosition NEQ 0>

                    <cfset changedIDs = ListDeleteAt(changedIDs, IDPosition)>

                </cfif>

            </cfif>

            <!--- run the update query on all records (I've tried it on all and on only the changed records and it seems to work fine either way, so I believe the problem is with the list functions above) --->

            <cfquery datasource="ctband">

            UPDATE Files

            SET Display = #displayNew#

            WHERE ID = #ID#

            </cfquery>

        </cfloop>

    <!--- set the original and changed lists as url parameters to check if successful --->

    <cflocation url="/admin/filelist_update_confirm.cfm?startlist=#form.listofIDs#&en dlist=#changedIDs#">

    </cfif>

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 10, 2012 6:57 AM   in reply to jawmusic

    I can run a query of queries

     

    I would not bother with a QoQ unless you need the data for something else in the page. Just run two database queries: one to retrieve "yes" records and another to retrieve the "no" records.

     

     

     

    What I'm trying to do is somehow record which records were changed, either from yes to no, or no to yes

     

    There are several different ways you could slice it. But technically you could derive that information with just two lists: ids marked "yes" (before any changes) and ids marked "yes" (after the changes).

     

             - Id's that exist in the original, but not the new list, were changed from "yes" -> "no"

             - Id's that exist in the new list, but not the original, were changed from "no" -> "yes"

     

     

    Is there a problem using that function in that position inside the query?

     

    Yes, databases knows nothing about CF functions and vice versa. Within a cfquery, the ColdFusion server evaluates any CF code first. The server translates those expressions into simple strings. Then sends the final sql string off to your database for execution. So you cannot use CF functions on a database column because it is evaluated long before you are even connected to the database.

     

    Like I mentioned above, you should use a WHERE IN (...) clause to return multiple records by id. Just with a SELECT rather than UPDATE. See the example and link above.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 10, 2012 8:29 PM   in reply to jawmusic

    The forums automatically send email notifications when new responses are posted (but not edits or deletes). So those responding via email do not see those kinds of changes. Nothing is ever really deleted on the internet

     

     

    Anyway, glad you resolved it. It is always nice when the solution turns out to be simpler than you thought.

     

     

    -Leigh

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 14, 2012 12:56 AM   in reply to jawmusic

    jawmusic wrote:

     

        <cfif IsDefined("form.display#ID#")>

          <cfset displayNew = -1>

        <cfelse>

          <cfset displayNew = 0>

        </cfif> 

    This code is in a section that has already confirmed that the form has been submitted. Hence IsDefined("form.display#ID#") will always be True.  Did you perhaps intend to say something like:

     

    <cfset displayNew = -1>

    <cfif form["display#ID#"] NEQ -1>

          <cfset displayNew = 0>

    </cfif> 

     

    WHERE ID = #ID#

     

    Didn't you say earlier that you had to correct this into WHERE ID IN (...)?

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 14, 2012 12:36 PM   in reply to jawmusic

    Why use individually named checkboxes at all? The "same name" checkboxes approach we discussed earlier is much simpler. When the form is submitted the checkbox value will be a list of ID's. Simply compare that to another list to find the differences. Then run your UPDATE queries with WHERE IN (...)

     

                  <!--- for brevity omitted cfqueryparam --->

                  UPDATE Table SET Display = 0 WHERE ID IN ( #listOfUNcheckedIDs# )

                  UPDATE Table SET Display = 1 WHERE ID IN ( #listOfCheckedIDs# )

     

    jawmusic wrote:

    However, it seems that the variable "listofIDs" set below is being set to ALL the items in the table, instead of only those currently being displayed based on the filters. Therefore, when the update query processes those items, and cannot find the corresponding checkbox, it's being set to 0.

    But to answer your question, the list is populated from the "file_listing" query. That means the query is returning more records than you think.  Most likely because the url filters are not defined when you submit the form. The first thing you should do is comment out the cflocation so you can see what is actually happening.  Add the "result" attribute to your cfquery, so you can view the generated sql.

     

                  <cfquery name="file_listing" datasource="ctband" result="yourResultName"> .....

     

    Then dump the result, URL and FORM scopes: What are the results?

     

                       <cfdump var="#URL#" label="URL">

                       <cfdump var="#FORM#" label="FORM">

                       <cfdump var="#yourResultName#" label="Result">

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 14, 2012 11:55 PM   in reply to jawmusic

    jawmusic wrote:

     

    The “Where In” was on the confirmation page in order to display info about which records were changed. This is on the action page where the records are actually updated. As for that “IsDefined” statement, that’s for the checkbox, so it’s only defined if it’s currently checked, unless I’m mistaken.

    What you say is justified.

     

     

    I’m pretty sure the issue isn’t actually with that checkbox, it’s that the update query is updating all the records in the default query (before the filters are changed) instead of only the records displayed after the filter fields are changed. For example, when the page first loads, it’s loading all files uploaded in the last 6 months. Then you can filter those results using selection fields to narrow the display down to a certain ensemble (marching_band, percussion, guard), selection (Part I, Part II, etc.) or category (music, visual, etc.). The filters are working fine for the display, they’re just not filtering the list of IDs that get processed through the update query. I thought once I moved  etc.), but that didn’t work either.

     

    Quite clear.However, I suspect the problem is actually with the display! Your code, <cfset displayOld = file_listing.Display>, treats display as if there is just one value. There is one for every row of data.

     

    I therefore expected to see each ID being associated with the display value from the same row. Something like this:

     

    <cfif structKeyExists(form, "submit")>

        <cfset listofIDs = ValueList(file_listing.ID)>  

        <cfset rowNo = 1>

     

        <cfloop index="ID" list="#listofIDs#">

            <!--- Associate each display with the corresponding ID --->

            <cfset displayOld = file_listing.Display[rowNo]>

            <cfif IsDefined("form.display#ID#")>

                <cfset displayNew = -1>

                <cfelse>

                <cfset displayNew = 0>

                </cfif>

                <cfif displayNew NEQ displayOld>

                <cfquery datasource="ctband">

                UPDATE Files

                SET Display = #displayNew#

                WHERE ID = #ID#

                </cfquery>

            </cfif>

            <cfset rowNo = rowNo+1>

        </cfloop>

        <cflocation url="/admin/admin.cfm">

    </cfif>

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 15, 2012 1:36 AM   in reply to BKBK

    Your code, <cfset displayOld = file_listing.Display>, treats display as if there is just one value.

     

     

    Good spot. You need to loop through both values ie use a a query loop.  That said, unless there are other factors we are unaware of, the IN (...) approach is simpler IMO.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 15, 2012 11:19 AM   in reply to jawmusic

    jawmusic wrote:

     

    What I don’t understand, and I suppose it’s because this is all so new to me, is why the “ would be a problem. Since it’s inside the loop, isn’t it being set to a single value on each cycle of the loop? And wouldn’t that value correspond to the initial value of the Display column for that particular entry? Why then would that be causing problems? For each cycle of the loop, it should only have one value, the value that corresponds to that record’s ID. Am I missing something?

     

    No, because it is inside a list loop. That only affects your list of id's. It has no impact on the query:

     

               <cfset displayOld = file_listing.Display>

     

    If you do a little debugging (which I highly recommend) and output the values inside your loop you will probably see the #displayOld# value never changes as BKBK mentioned. It always points to the value in the first row of your query. You need to use a query loop instead, so you can iterate through both values:

     

         <cfloop query="file_listing">

           <cfoutput>

              debugging - current ID = #ID# old display value = #display# <br />

          </cfoutput>

         </cfloop>

     

    I guess I’m not really sure how you are suggesting I use the IN (...) clause in this situation.

    ...

    I just made a hidden field in the form like this:

     

    Good. Now if you give all of the checkboxes the same name ie "selectedIDs"

     

                     <input type="checkbox" name="selectedIDs" value="#ID#">

     

    you will end up with two lists on your action page:  form.listOfIDs (ids that were selected) and form.selectedIDs (new id's after changes). You can compare those two lists to derive what changed. For a more detailed explanation reread my responses from Jun 10, 2012 6:57 AM  and Jun 14, 2012 12:36 PM.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points