Storing checkbox values in a MySQL database then retrieving whether they're set or not... what is the best method?

Question

Hi everyone

I have a form where users can submit sites and say if they work in different browsers. Currently, it looks like this:

<input type=checkbox name="browsers[]"  value="IE6"/>Internet Explorer 6<br/> 

<input type=checkbox name="browsers[]"  value="IE7"/>Internet Explorer 7<br/> 
<input type=checkbox name="browsers[]"  value="IE8" checked="checked"/>Internet Explorer 8<br/> <br/>
<input type=checkbox name="browsers[]"  value="FF2" checked="checked"/>Firefox 2<br/> 
<input type=checkbox name="browsers[]"  value="FF3" checked="checked"/>Firefox 3<br/> <br/>
<input type=checkbox name="browsers[]"  value="SA3" checked="checked"/>Safari 3<br/> 
<input type=checkbox name="browsers[]"  value="SA4" checked="checked"/>Safari 4<br/> <br/>
<input type=checkbox name="browsers[]"  value="CHR" checked="checked"/>Chrome<br/>  <br/>
<input type=checkbox name="browsers[]"  value="OPE" checked="checked"/>Opera<br/>  <br/>
<input type=checkbox name="browsers[]"  value="OTH" />Other Browsers<br/>  <br/>

What I want to know is, what's the most efficient way of storing checkbox values in a database? When a user browsers to the site's page on my site, I want it to query the database and return which sites it's compatible with. Currently I'm thinking of doing it with something like this:

<?php if (!$browsers['FF2'] = NULL) {
                //(Insert into field named 'FF2')

            } else {
                //(Keep field named 'FF2' as NULL)
            } ?>

Then when visiting the site's page, it would just check through each database column (FF2, FF3, SA4, etc.) to see if the value isn't NULL. If not, it'll echo 'Compatible with FF2' etc.

This strikes me as being slightly illogical. Having a seperate column for each browser seems a little funny - I don't know if this is me being skeptical or if that really is the easiest way to go about it. Can anyone suggest some other methods?

Thanks!

Jack

Answer 1

To provide flexibility, I would do a many-to-many relationship.

So, e.g. have a Support table with browserId and websiteId foreign keys, meaning the website corresponding to websiteId supports the browser corresponding to browserId. Then obviously have browser and website tables with the ids and other required information.

Answer 2

You asked many questions so I'll answer this one:
"What I want to know is, what's the most efficient way of storing checkbox values in a database?"

You could use MySQL's BOOLEAN... however, you could probably optimize by storing all your data in one variable [bitwise shifting and a long integer?] since MySQL's Boolean is really 8 bits [a short int]... If you have 10 true/false values, thats using 80 bits if you use MySQL's Boolean, where in reality, you only need 10.

First bit: Works in ie7
second bit: Works in ie8
...etc

Answer 3

It depends on how often you think your options will have to change.

In general, having many similar fields is very simple, but too flexible way of storing data. The alternative option is to create a table for the possible values (one record each) and link them to the user record with a link table. This is more flexible, however, more complex solution and puts more load on the database server.

Google for 'One True Lookup Table' for hints on this.