Webmaster Forum / ColdFusion / Advanced Techniques / April 2007

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?

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

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

Id_freind is a numeric field I hope.

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)

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)

?

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

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.

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

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

you can submit the form to see the error im getting

thanks again

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.

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

thanks

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

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

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.

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

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?

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.

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.

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.

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