More than 1 value in 1 column

Paragon

I wanted to have multiple check boxes and I want people to beable to select multiple boxes.

So I was thinking I could store it in the database in one column, but I of course havn't done it before so wanted to get a little help.

Now I figured I could seperate the options by commas and insert it into the database that way.

What would be the best type of column to use? I am using a MYSQL database. Text?

Anybody that gots some extra time hit me up.

drawmack

You can use a numeric field if you lay it out like so:

Option 1 is numbered 1
Option 2 is numbered 2
Option 3 is numbered 4
Option 4 is numbered 8
Option 5 is numbered 16
Option n is numbered ((n-1)²⁾

Then you just add up the numbers they have put a check in and every combination will have a unique number. However backtracking is a little difficult.

I recommend having a different field for each option then having the values be 0 or 1 for off and on respectively.

onion2k

Use a seperate table .. 2 columns.. user_id and checkbox_value. One record per checkbox.. That way you can pull out all the users with a particular checkbox ticked without having to pull out the records of all the users.

(There is another way, using an int column and bitwise shifting in your SQL, but its very, very complicated..)

drawmack

wouldn't you actually need three columns in that other table:

user_id
checkbox_id
checkbox_value

Paragon

I recommend having a different field for each option then having the values be 0 or 1 for off and on respectively.

I would love to, but the check boxes are going to be dynamic as well. I don't exactly know how many will be there.

drawmack

Okay then create two tables

checkboxes
ID -> autonumber
disp_name

checkboxvalues
ID -> autonumebr
UserID
CB_ID
value

Paragon

So basicly its not good to put it into one column?

I am talkin about the checkbox selected column here.

checkbox column of selected boxs 1,3,4,7,11

These are the ids of the values that are already in another table. That will display all the check boxs dynamicly for the user to select.

ID | NAME
1 | name1
2 | name2
3 | name3
ect...

Or is this bad form?

tekky

multiple data into 1 column is easily done with SET columns....

depending on the information I suppose... but anything is really storeable....

say you have the options of

red, blue, green, orange (multiple are selectable)

UPDATE table SET column = ('red', 'blue', 'green', 'orange') WHERE id= $id

or... done using BITFlag type assignments....

red = 1, blue = 2, green = 4, orange = 8
if all options are clicked, SET column = 15 (1+2+4+8)
if only red and orange, column = 9 (1+8)

selecting the column can return the flag names you assigned when creating the column, OR the value of the options stored in that column

you are also able to select rows that contain any one, or combinations of those values, or none of the values using the bitwise operators....

I use em... dunno many other people that do... but I like them for things like that 😉

Paragon

Thats sounds kinda cool, but I am not sure how that would work. What if a option was deleted and new ones dynamicly created via admin panel for example.

I am gonna have to do a search on that to learn more about it.

tekky

that would actually be a bit difficult i suppose... in which case a seperate table for the flags would seem to be needed...

dnast

I think Onion2k's suggestion would be the best. I tried the comma separated way with fields that could be changed, and everything worked out okay. It's just that later on there were some other features that could've been easily added with proper normalization.

Check out the article on this site. Even though you'll probably be okay with comma-separated fields, in the future you'll be glad you didn't use them.

ainars

I have a similar problem. I am going to use checkboxes in form and values (only 0 or 1) will be stored in the same column in form of:

1,0,1,1,0

It s OK so far. The problem is that I will use search form where users will be able to check multiple boxes for searching. Its easy if there is only one value in column:

SELECT * FROM table WHERE value='1' ...

But I dont know what query to use to select from a column which contains multipla values separated by commas and if user checked multiple boxes in searching form.

Thanks.

Weedpacket

Originally posted by ainars
But I dont know what query to use to select from a column which contains multipla values separated by commas and if user checked multiple boxes in searching form.

That's why it's a bad idea to store them that way.

ainars

sorry, it was a mistake - values will not be only 1 and 0 - each checkbox will have a different value and only values of ckecked boxes will be inserted in database in form of:

2,3,5,6,9,11,15

some people suggest to put each value in a different column, but I have a large questionaire with some 120-150 checkboxes. is it a good idea to create a table with 150 columns for each value?