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
MySQL: Storing checkbox values
Moderator: General Moderators
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
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.
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?
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
Well to find say an id number in the field you can just do:
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.
Code: Select all
field LIKE '%id_number|%'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.
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?
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?
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany