Getting input from 50+ check boxes on a web form

Think of the form I am working on as a type of survey. It has 50+ possible conditions which can all be checked - or one. Just like any form I need to be able to post the results to a MySQL database. I am trying to figure the best met开发者_如何学运维hod to get the input from all these checkboxes without having to create individual columns for each one (unless that is considered best practice).

So here is what I was thinking could work. Assign the checkboxes name numerically from 1-N. Then iterate through every checkbox checking it's state. Then instead of making a column for each checkbox - use one column and concatenate some kind of identifier for each.

Psuedocode

For checkbox in N:
    check state of N:
        if N is checked:
            concatenated string += idenifier(N)

Is this method a good way of doing it, or do you have other suggestions?

This is probably the worst way of doing it. There are lots of things wrong with your proposed solution - but rather than try to make a silk purse from a sow's ear....

The only sensible way to do it in a relational database would be to have each checkbox result stored in its own row in the table. So you might have something like:

response {
   id auto_increment-,
   user_id references data elsewhere,
   ...
   primary key (id)
}

answers {
   response_id references response.id,
   checkbox_id references available_checkboxes.id,
   value_selected,
   primary key(response_id, check_box id)
}

Then you can also describe what the checkboxes represent:

available_checkboxes {
    id auto_increment;
    option_text varchar(40);
}

And build different questionnaires....customize the data gathering....and more

Your question isn't really about your form, it's about how to store your data. It sounds like you'd like to store what might be considered a sparse matrix, ie you only want to store non-empty values. Have a look at the EAV model. It's useful for data like this though it does have some major limitations that you should make yourself aware of.

The table structure essentially makes columns into rows. Something like this:

CREATE TABLE terms(
  term_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  termname varchar(30) NOT NULL,
  termvalue varchar(100) NOT NULL,
  assoc_id INT UNSIGNED NOT NULL,
  CONSTRAINT FOREIGN KEY (assoc_id) REFERENCES maintable(main_id) ON DELETE CASCADE)
ENGINE=InnoDB

So you can call your checkboxes whatever you like. When you process them, there will be 2 insert queries- one to create the main record; and one to store the associated checkbox values.

INSERT INTO maintable(allfields, that, must, be, collected) VALUES (1,2,3,4,5);
//get last_insert_id(), we'll say main_id=7 this time

INSERT INTO mytable(termname, termvalue, assoc_id)
VALUES
('colour', 'blue', 7),
('feeling','happy', 7),
('schedule', 'daily', 7);

Using separate columns in your DB should be determined by what the information is and how you plan to use it in the future.

If you are collecting info that is unique and could be used individually... that is, if each checkbox represents a value that could be compiled and then used apart from the others, then I would use separate columns.

EDIT

As per your comment...

What would be the benefit of this method if I can get all the same information from the concatenated string just as long as I have an extra parsing step when getting the data?

I can think of several reasons:

1. You might not know how you want to retrieve the information in the future. Placing it in one field would make it much more difficult to get, depending on how you wish to grab it.

2. Comparing the info becomes much harder, should you wish to do that.

3. Adding or removing checkboxes would also become much harder to maintain.

My suggestion would be to create a field varchar(50) in the MySQL table. Each position would be 0 or 1 depending if the associated checkbox was checked or not. So if the user checked boxes 1, 2, 5, 10, and 50, your string would look like:

$string = "1100100001000...0001" <- 50 positions

You could create the iteration in the receiving code to check if the checkbox was checked or not. If it was checked, add a "1" to the string, if it was not checked add a "0".

You could even create the string in your validation code in the client with javascript and pass the string already ready to be stored.

I have seen similar implementation used to track payments in an installment plan - each position in the string representing a monthly payment.

I hope this helps.

Probably, the most clean way to doing that, it's to save form results as ORM-object, so checkboxes would be accessible like a form result's properties.

You could use Doctrine, or MongoDB for that.