21 Replies Latest reply on Apr 26, 2007 11:26 AM by namtax

    updating multiple fields at once

    namtax Level 1

      i have a form with a check box

      <cfinput type="checkbox" name="update_friend" value="1" checked="yes">

      then on the action page my query reads

      <cfparam name="FORM.update_friend" default="0">

      <cfquery datasource="#application.datasource#">
      UPDATE user_friend
      SET update_friend = #FORM.update_friend#
      WHERE id_friend =#FORM.id_friend# (THIS IS THE PRIMARY KEY FOR THE TABLE I AM TRYING TO UPDATE)
      </cfquery>

      when i submit this, i am getting a mysql error message,
      any help would be appreciated

      thanks
        • 1. Re:  updating multiple fields at once
          Dan Bracuk Level 5
          What is the sql that is being set to the db? You should see it on your debugging info. Also, what is the error message?
          • 2. Re:  updating multiple fields at once
            namtax Level 1
            hi dan..

            i have 7 people in my form...and if i check the checkboxes for four of them, then
            submit, i am met with this error message

            Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1,1,1 WHERE id_friend =115,117,119,112,113,121,123' at line 2

            The error occurred in C:\Domains\musicexplained.co.uk\wwwroot\u\updates_action.cfm: line 6

            4 : UPDATE user_friend
            5 : SET update_friend = #FORM.update_friend#
            6 : WHERE id_friend =#FORM.id_friend#
            7 : </cfquery>

            this would maybe suggest that the <cfparam name="FORM.update_friend" default="0"> is not functioning as well...

            essentially, it seems like its not allowing me to update multiple rows at the same time...is the answer to use a cfloop around the update sql?

            thanks
            • 3. Re:  updating multiple fields at once
              Dan Bracuk Level 5
              Replace the equal sign with the sql keyword "in". Enclose your list with parentheses.

              Id_freind is a numeric field I hope.
              • 4. Re:  updating multiple fields at once
                namtax Level 1
                hi dan

                yes id_friend is a numeric field

                i have changed the query to

                <cfquery datasource="#application.datasource#">
                UPDATE user_friend
                SET update_friend = (#FORM.update_friend#)
                WHERE id_friend IN (#FORM.id_friend#)
                </cfquery>

                any clues? thanks

                but am now gettin the error

                General error: Operand should contain 1 column(s)
                • 5. Re:  updating multiple fields at once
                  insuractive Level 3
                  is update_friend an integer field or a text field? Remember that if you have multiple fields in an HTML form, they will be returned as 1 comma delimited list. Are you trying to do this:

                  UPDATE user_friend
                  SET update_friend = 1
                  WHERE id_friend IN (12,13,14)

                  OR

                  UPDATE user_friend
                  SET update_friend = '1,1,1'
                  WHERE id_friend IN (12,13,14)

                  ?
                  • 6. Re:  updating multiple fields at once
                    namtax Level 1
                    hi michael, thanks for your response her......

                    update_friend is an interger field......

                    i am essentially trying to do this

                    UPDATE user_friend
                    SET update_friend = '1,1,1'
                    WHERE id_friend IN (12,13,14)

                    cheers
                    • 7. Re:  updating multiple fields at once
                      Dan Bracuk Level 5
                      That would be a problem because '1,1,1' is a string and update_freind is an integer field. Hard to tell based on the information given, but you might have a poor database design for what you are trying to accomplish.
                      • 8. Re:  updating multiple fields at once
                        insuractive Level 3
                        Are you really trying to set the value '1,1,1'? If so, what are you using that for? or would you like to set update_friend = 1 for each of the three friend id records?

                        In which case:

                        UPDATE user_friend
                        SET update_friend = 1
                        WHERE id_friend IN (12,13,14)

                        Will set update_friend to 1 in friend records 12, 13 and 14
                        • 9. Re:  updating multiple fields at once
                          namtax Level 1
                          ok let me explain a bit more...

                          i have a system which shows what your friends have been doing on my website...what artists they have added into the database etc(Updates)....what i am trying to do is allow people to choose which friends they would like to recieve updates for...

                          so i have a column called update_friend which is set to default 1 in the friends table...then I have a query on the updates page which shows the activity of all of your friends with a 1 in the update_friend column...

                          to allow people to control which friends they recieve updates from I have a form which checkboxes

                          <a href=" http://www.musicexplained.co.uk/delete/updates_opt.cfm">click</a>

                          if you want to remove a friend from your updates list you would decheck the checkbox next to their name and then submit...

                          what i would then like to happen.....is for the query to update the checked check boxes with 1 so the user will still recieve updates from those people, and update the unchecked check boxes with 0 so they no longer recieve updates from them

                          if I have 4 checked check boxes out of the 7 i am getting a list (1,1,1,1) as an output, which would suggest initially that the <cfparam name="FORM.update_friend" default="0"> isnt working, but regardless of this I would like if john was checked, dave was checked, mary was checked and james was checked, and then hilary, donny and gas was unchecked.......john, dave, mary and james would be updated with 1 in their update_friend columns, and the rest would be updated with 0 ..

                          so essentially the query would be

                          UPDATE user_friend
                          SET update_friend = 1,1,1,1,0,0,0
                          WHERE id_friend IN (12,13,14,15,16,17,18)

                          thanks





                          • 10. Re:  updating multiple fields at once
                            namtax Level 1
                            you can submit the form to see the error im getting

                            thanks again
                            • 11. Re:  updating multiple fields at once
                              amers Level 1
                              You're probably going to have to loop through the IDs and what you want to set them to.

                              "UPDATE user_friend
                              SET update_friend = 1,1,1,1,0,0,0
                              WHERE id_friend IN (12,13,14,15,16,17,18)"

                              This query updates the field update_friend to equal 1,1,1,1,0,0,0 for the id_friend(s) you listed below.
                              • 12. Re:  updating multiple fields at once
                                namtax Level 1
                                hi amers, how would i go about looping through the ids? would i use a cfloop?

                                thanks
                                • 13. Re:  updating multiple fields at once
                                  amers Level 1
                                  yes, I'd use cfloop with the list attribute. That would seem the easiest, not knowing exactly how your form works.
                                  • 14. Re:  updating multiple fields at once
                                    namtax Level 1
                                    would it be possible to get an example of the cfloop?
                                    what would you need to know about how the form is working??
                                    im pretty stuck here as you may have guessed

                                    cheers
                                    • 15. Re:  updating multiple fields at once
                                      kwillis
                                      i have done this many times and looping is the answer. for each user_id set it as the answer - example( <cfset userid_1 = 1> <cfset userid_2 = 1> <cfset userid_3 = 0>. send the number of elements through and loop through the elements and Evaluate the element. example <cfset useranswer = Evaluate(userid_1)>. in the loop the useranswer should be 1 or 0 based on the value set. it has been awhile since i implimented this but idea is solid and works but the syntax may be off a bit.
                                      • 16. Re:  updating multiple fields at once
                                        insuractive Level 3
                                        I may be misinterpreting the issue here, but what about trying something like this:

                                        On the form, instead of having the checkbox's value = 1, set the checkbox's value = the friend ID

                                        That way, on your action page, you will have a comma-delimited list of friends you want to be updated on. Then its just a matter of the following: Remove all friend records in the user_friend table for a given user, then insert only those friends for whom a user selected the update option:

                                        DELETE from user_friend
                                        WHERE user_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#myUserID#">

                                        INSERT INTO user_friend
                                        Set update_friend = 1
                                        WHERE user_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#myUserID#">
                                        AND id_friend in (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Form.update_friend#" list="Yes">)

                                        If you wanted to make sure each friend had an entry in the table, you could do the following:
                                        1) Set All update_friend values = 0
                                        2) update user_friend so that friend id's in the Form.update_friend list have update_friend = 1
                                        • 17. Re:  updating multiple fields at once
                                          kwillis Level 1
                                          insuractive: i like your solution, but i have a question. what if it is checked and they want to uncheck? i am also not sure what he is trying to accomplish ultimately and he has not stated if the checkbox is or is not populated during this process. is your solution assuming one state over another?
                                          • 18. Re:  updating multiple fields at once
                                            amers Level 1
                                            The easiest way to do work with checkboxes and whether or not they're checked or not, is to do a delete and do a clean insert - rather than updating each and every one.

                                            but, in reality, either works.
                                            • 19. Re:  updating multiple fields at once
                                              kwillis Level 1
                                              alot of ways to skin this cat. i not a cat hater either. i'll concider your answer next time i have to do this function.
                                              • 20. Re:  updating multiple fields at once
                                                insuractive Level 3
                                                amers gets at the idea I was going for - basically, your checkboxes are going to result in a comma-delimited list of the items you are going to set = 1. Your best bet is then to either clear everthing and only insert the items in that list, or conversely, you could set everything = 0 and perform an update where you set the records associated with the items in that list = 1. Either way, it handles the checked-to-unchecked situation without having to do any complication what-state-is-this-record-in testing.
                                                • 21. Re:  updating multiple fields at once
                                                  namtax Level 1
                                                  insuractive answer was spot on....thanks a lot for that....
                                                  yes I agree now that the best thing to do is to do a delete and clean insert

                                                  very much appreciated for this