在数据库中存储多个复选框值并在以后使用它的最佳方法是什么

Question

I am working on a website and i want to add a new section to it that will contain an advanced form for storing that data. There will be about 20-30 fields and few of them will be represented by multiple choice checkboxes eq array values on submit from these choices. This fields will have a fixed amount of choices that will never change and never added new ones into it. For example field one will have choice1, choice2, choice3. Field two will have 10 of them and field 3 will have 5.

Isnt a creating separate database tables for them and referencing them by its id a bit overkill? Would not it be better to save them as for example comma separated values under varchar field eq implode and eplode them every time i want to acces or store new one?

Also i have a bit trouble figuring out how to work with it later on. Since if i save them as as separate table and reference it by its id than my query will output every combiation of these fields. Which would result in for example 150 rows for one record. How can i convert it back to the single array value with nested array for this sub multiple values? for example 'id'=>1, 'title' => 'Lorem Ipsum', 'choices' => array('choice2','choice2').....

---

update: To better illustrate what i want to do i have created a simplified mockup of the form here -> http://www.anony.ws/i/2013/08/13/SsD5A.gif Its not actual form i will be using since i did not build it yet but it is close enough. You can ignore other fields i already know how i will save or work with them later on.

What i do need some help with is that checkbox groups. I need to store selected values and later on i will need to work with them. I will need to be able to search through them i will need to be able to display that data. I will also need to be able to create some comment system (i have already done that before so basically i just need to be able to reference data from this form by its database ID. Probably some updating and or other basic stuff.

Major thing is saving this choices and searching/displaying its data. Would be really geat if i can get the data from the databse in 1 array/obect with some nested arrays for this multiple choices. Something like i have sketched in the end of this post before the update.

Answer 1

Ask yourself the following question: Do I need to evaluate the choices done by the customers?

If you do, having them in plain comma separated lists will be a very ugly operation. If you really just want to log the choices, you can proceed with the implode/explode.

Doing it the normalized way:

Your table scheme:

checkbox_id | customer_id | value

When the customer submits his choices, you'll only insert the selected checkboxes:

foreach (checkbox in POST) {
    INSERT IGNORE INTO `table` (`checkbox_id`, `customer_id`, `value`) VALUES (checkbox, customer, 1)
}

If you want to read the customer's choices later on, go for:

SELECT `checkbox_id`, `value` FROM `table` WHERE `customer_id` = ? ORDER BY `checkbox_id`, `value`

This will get you a list of all choices done by the specified customer. If you need them in an array, go for some function:

function array returnChoicesAsArray(query, checkbox_id) {
    array = [];

    foreach (row in query) {
        if (row.checkbox_id == checkbox_id)
            array.add(row.value);
    }

    return array;
}

and call them for each of your checkboxes.

If you want to evaluate the choices for checkboxes, you can easily query on it:

SELECT COUNT(*) FROM `table` GROUP BY `checkbox_id`