Inserting multiple checkbox values into db

[chaeks]

i am struggling to insert multiple checkbox values into a database table using one update statement. i guess that i will need to create and array to hold the values and then insert them into the db table. can anyone help with sample code?

[Daverms]

Hi,

No need to create arrays and all.. Even though your check box holds various values if you name them uniquely it will inturn will get saved as a comma delimited values in DB...

[Dan_Bracuk]

quote:

---

Originally posted by: Daverms
Hi,

No need to create arrays and all.. Even though your check box holds various values if you name them uniquely it will inturn will get saved as a comma delimited values in DB...

---

Actually, you have to give all your checkboxes the same name to get that list. Storing the list as in a single record is not necessarily the best idea.

For the original poster. Are you trying to insert new records or update existing ones?

[MichaelSJudd]

You didn't say what DB - here's what I do for MSSQL.

Name the checkboxes the same as the fields you want to insert.

If this is actually an update to an existing record, you will need to include any key fields as hidden fields in the form.

Either way, simply use either:

<cfinsert datasource="yourdatasourcename" table="yourtablename" formfields="field1,field2,field3">

OR (for an update):

<cfupdate datasource="yourdatasourcename" table="yourtablename" formfields="primarykeyfield,field1,field2,field3">

Hope this helps.

- Mike

[chaeks]

HI guys,

Thanks a stack for the info. I am using MySQL v 5 and trying to insert and later update info to a table. For example I have employees table, hobbies table and emp_hobbies table. Each employee can select a number of hobbies taken from hobbies table and populated into an array and displayed as checkboxes.

Once they select the checkboxes it must insert (and later update) the emp_hobbies table by adding a row for every employee and their hobbies selected. Each cjheckbox has its own hobby_id from the hobbies table and this is the id that needs to be inserted into emp_hobbies with the employees unique id (emp_id).

[Dan_Bracuk]

For the first part, I believe that mySql supports this kind of syntax:

insert into mytable
(field1, field2)
values
((value11, value12),(value21, value22)) etc.

To populate the emp_hobbies table, it might be simpler to delete all the existing rows and re-insert new ones.

[chaeks]

Thanks Dan - got the syntax sorted.

However, is there a way to insert multiple rows into a table in one insert statement so that for each hobby that an employee chooses he/she has a seperate row with their emp_id and then also the hobby_id?

If an employee chooses 3 hobbies the emp_hobby table must contain 3 rows for that employee.

All the checkboxes have the same name - hobby - in nthe form.

[Dan_Bracuk]

Since you have the syntax down, the next step is to realize that form.hobby will either not exist at all, or it will be a list. The cfloop pages in the cfml reference manual have a section on looping through a list. If you don't have one, the internet does.

[chaeks]

Thanks Dan - appreciate your help!