MySQL: Storing checkbox values

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
wurb
Forum Newbie
Posts: 4
Joined: Mon Oct 25, 2004 7:04 pm

MySQL: Storing checkbox values

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
wurb
Forum Newbie
Posts: 4
Joined: Mon Oct 25, 2004 7:04 pm

Post 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?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
wurb
Forum Newbie
Posts: 4
Joined: Mon Oct 25, 2004 7:04 pm

Post 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?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
wurb
Forum Newbie
Posts: 4
Joined: Mon Oct 25, 2004 7:04 pm

Post by wurb »

Really? You can't say that one of them slows the database more than the other?
Post Reply