Multiple values into a single field

[ktmx]

Hi, I've created a form that is all radio buttons, each question looks like:

<legend>Choose the option which best describes your hair type:</legend>
<cfinput type="radio" name="hairtype" value="8"> Dry hair<br>
<cfinput type="radio" name="hairtype" value="9">Oily Hair<br>
<cfinput type="radio" name="hairtype" value="10">Normal hair<br>
<cfinput type="radio" name="hairtype" value="11">Sensitive Scalp<br>
</fieldset>

Once submitted the form gives me a list of values (answers) which looks like
this: (1,2,3,4).
I want to insert these values, in a comma delimited list, into one field in my
database, but coldfusion wants the same amount of fields as there are values...

My processing code:
<cfquery name="InsertPref" datasource="030902ics3kla">
INSERT into People (Pref)
VALUES (#Form.skintype#, #form.hairtype#, #form.haircolour#)
WHERE ID= #Cookie.BodyShop#
</cfquery>

Any ideas?
Katie

[mat1492]

Hi KAtie,

The list is fine. Coldfusion is just expecting three fields from that form,
skintype, hairtype, and haircolour. If they are not filled in in the form they
will not be present. If you use "cfparam" to set default values on your submit
page than if they are not selected they will be inserted with the default
values! You could also set up those other fields as hidden fields in your form
and submit a value that way. Many ways to skin a cat!

Mike

[paross1]

First of all, it looks like your INSERT statement is incorrect, since you have
an unnecessary WHERE clause. Also, I will make the observation that you have a
less than desirable data model since your design requires multiple values of
hairtype in a single database field. This is a normalization issue, and should
probably be resolved by migrating your hairtype to a separate table linked by
an associative entity. As for implementing it as designed, I'll let someone
else explain how you can insert this LOV into this field - probably need to
include single quotes around each item, then use preservesinglequotes in your
insert statement.

Phil

[mat1492]

Katie,

Sorry shold have looked at all your code before answering. the correct format
for inserting would be:

<cfquery name="InsertPref" datasource="030902ics3kla">
INSERT into People (skintype, hairtype, haircolour)
VALUES ('#Form.skintype#', '#form.hairtype#', '#form.haircolour#')
WHERE ID= #Cookie.BodyShop#
</cfquery>

You need to tell it which fields in the databse to insert the form fields
into. Given the table field names are the same!

Mike

[paross1]

Mike,

Without the WHERE clause, of course, unless you replace the VALUES with a SELECT..FROM..WHERE.

Phil

[mat1492]

Phil,

Your right! You only need to use the "where" clause if you were updating the record not inserting it. I really should read the whole post before replying:)

Mike

[paross1]

Also, technically, you don't need to explicitly include the field names if the
VALUES clause contains values for all of the fields in the table in the correct
order. The danger of doing this, however, is if your ever alter your table and
add new fields, then you would have to alter every insert statement in every
template to add the field value, even if it was not a mandatory field. The safe
way is to do as Mike suggested and explicitly list the fields, but I just
wanted you to know that it is only necessary if you are inserting rows where
your insert statement does not contain a value for each field in the table.

Phil