Checkboxes

[[deleted]]

I need some help with a database design question if you have time!

I am working on a project where a student is going to pick a number (21-26) of choices from a list of check boxes and then submit them to a database.

Right now, I have each subject as a separate field in the database. When I submit the selections I get a series of errors telling me that there are undefined variables which indictes to me that the query as I have it set up right now needs for each checkbox to be marked or have another value attached to it if the check box is to remain unchecked.

I guess there is a series of questions in this situation!

    1.  Is this the best way to set up the database or should I have one field and then have the values entered in as a string where 0 is the value for unchecked and 1 is the value for checked and the string would look something like 0010100001001...., then the question is how do I recall these values back when I query which students have selected Math (General), for instance, which is the 13th place in this string.

   1b.  How do I go about inputting the values from a series of check boxes into a single string with the proper placement.

    2.  If I keep the structure of the database the same how do I tell the query that there really is a value to the unchecked box (0).  I guess I will need to know this for either scenario.  

If there is a better solutio than check boxes I'd be open to that to at this point!

Thanks for any help you can give

[sarahk]

firstly, your structure.
yep, it needs changing but it's not urgent. The "right" way would be to have a table with
studentid, choicetype, choicevalue

that way you can extend or restrict the range without causing yourself grief. If the student doesn't select anything, you don't insert into the table. The only time you'd end up with a 0 is if the student later chose not to have that option.

Regardless of whether you want to change your structure you need to get smarter code.

If a check box is unselected no value will be sent by the browser
$sql1 = "insert into mytable (id,";
$sql2 = " values ( null, ";

$sql1 .= "option1,"
if (isset($check1))
$sql2 .= "1, ";
else
$sql2 .= "0, ";

// process all the check boxes in the same way - you can make this smarter but this is readable and works

// finally remove the last comma and append the closing ) and put the 2 strings together and send to the database.

If you change your structure you would be building up a series of insert statements and sending to the database in one big hit.

Good luck

[Anon]

to extend on what Sarah said, a table with

id, student_id, question_number, question_answer would be best - that way, you could easily add additional questions to the form, and the database would automatically be able to handle it. You could also easily pull up all answers for a particular question etc..

[[deleted]]

Thank you for your help!! I have got a lot to learn about programming and database structure so your positive input is great.
pem