Page 1 of 1

MySQL: Storing checkbox values

Posted: Mon Oct 25, 2004 7:30 pm
by wurb
I have a problem with storing information from checkboxes in a MySQL database. Let's take the example of a database with MP3 players. Every player has its own entry in the database. Now what I want is to record certain qualities that a player can possess. Every player can have up to ten qualities, like 'WMA support', 'Recording function', or 'FM Tuner'. To insert each product, I use a form on a php page and for the every possible quality I use a checkbox.

Now here's the problem. I have been searching for the best way to put these values in the database. Possible solutions:

1. Put the values in an array and store this in the database using SET
2. Create new columns for each possible quality. The values could be something like 'Y' and 'N' or '0' and '1'.
3. Create a new table that holds just the qualities. One column will hold the record ID from the original table, and another column will hold a number or whatever identifier for the quality.

Drawbacks for each solution:

1. From what I read, SET columns are not the most efficient ones, and will slow a search request.
2. Your table becomes huge. This can't be good for SELECT requests, right?
3. Linking the databases causes extra trouble. Also, every quality requires a new entry in the QUALITIES table. This is not efficient (I'd say), and again makes selecting records hard.

What do you think? Are these indeed possible solutions? Are there any other solutions?

I'd like to hear what you guys think!

Paul

Posted: Mon Oct 25, 2004 7:39 pm
by kettle_drum
Well i definiately wouldnt store the features as text in the database - so use either a second table that holds the name of the feature and an ID number, or an array hard coded in the page to hold the values. You can then use implode on the checkbox submitted to get a string of the values selected and then store these in the database. Then to display them grab the string from the database explode it and then get the name from the database or array.

Posted: Mon Oct 25, 2004 9:28 pm
by wurb
Thanks for your reply!

About the option of imploding the values into one string: doensn't this make it hard to select records that should meet certain conditions? I want to be able to select products that have certain features, but if all features are put in a single string this is kinda hard, right?

Why wouldn't you store the features as text btw? What's are the disadvantages?

Posted: Mon Oct 25, 2004 10:23 pm
by kettle_drum
Well to find say an id number in the field you can just do:

Code: Select all

field LIKE '%id_number|%'
Where i have used '|' to implode with.

I wouldnt hold the values as text due to:

1) Storage issues. If they have a lot of features then its a large string, instead of just a few numbers.
2) If you ever want to change 'can play MP3' to 'Can play mp3' you have to go through each and every record and edit the strings - instead of editing the value in a single place.
3) Then following on from 2, you will be repeating the same data over and over and so its not really a normalized database.

Posted: Tue Oct 26, 2004 6:39 am
by wurb
Thanks again! About looking for records using LIKE %...%: doesn't LIKE take way more time than '='? I'm just afraid I'll slow down the database.

Also, what about creating new fields in the table for each feature. So for the feature 'WMA support' I'd make a new column named 'WMA' and this could be either 'Y' or 'N'. Is this fast/effective?

Posted: Tue Oct 26, 2004 8:00 am
by CoderGoblin
This is rather a personal viewpoint. I would do a join within the database table. The select wouldn't be too hard and it makes the whole system far more flexible when new features are added.

Alternative if PHP is only used to access the DB. Have a serialised array as text within the DB.

Posted: Tue Oct 26, 2004 2:15 pm
by wurb
Really? You can't say that one of them slows the database more than the other?