• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

updating multiple fields at once

New Here ,
Apr 22, 2007 Apr 22, 2007

Copy link to clipboard

Copied


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
TOPICS
Advanced techniques

Views

1.7K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 22, 2007 Apr 22, 2007

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 23, 2007 Apr 23, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 23, 2007 Apr 23, 2007

Copy link to clipboard

Copied

Replace the equal sign with the sql keyword "in". Enclose your list with parentheses.

Id_freind is a numeric field I hope.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 23, 2007 Apr 23, 2007

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 23, 2007 Apr 23, 2007

Copy link to clipboard

Copied

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)

?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

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





Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

you can submit the form to see the error im getting

thanks again

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

hi amers, how would i go about looping through the ids? would i use a cfloop?

thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

yes, I'd use cfloop with the list attribute. That would seem the easiest, not knowing exactly how your form works.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 24, 2007 Apr 24, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 25, 2007 Apr 25, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 25, 2007 Apr 25, 2007

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 25, 2007 Apr 25, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 25, 2007 Apr 25, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 26, 2007 Apr 26, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 26, 2007 Apr 26, 2007

Copy link to clipboard

Copied

LATEST
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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation