I have a form that I use collect data using checkboxes.

example:

Check all "colors" that apply:
[] blue
[] orange
[] red
[] green
[] yellow

The users can select one option, two option,or any combination of any
of the choices.

I use the base 2 methodology. For example if someone were to choose
blue and orange I would sum the values 1+2=3. So 3 is the value which
is equal to blue and orange.

Or for example the stored value(31) is equal to 1+2+4+8+16, which is
equal to the user choosing blue,orange,red,green,yellow.

The storing of the data seems to be very effienct, but the problem is
retreival during queries. It's very easy to find all entries that
chose blue and orange b/c the mysql statement would be "Select * FROM
table WHERE colors=3;".

But I have no way to find all the choices that contain blue, which
would mean, choosing blue alone, or with any combination of any of the
other choices.

PLEASE HELP!!!! I am really stuck

From:	Alan
Subject:	Re: Multiple checkbox storage and retrieval
Date:	Mon, 17 Jan 2005 20:30:28 -0500

wrote in message
news:1106008217.017098.55850@z14g2000cwz.googlegroups.com...
> I have a form that I use collect data using checkboxes.
>
> example:
>
> Check all "colors" that apply:
> [] blue
> [] orange
> [] red
> [] green
> [] yellow
>
>
> The users can select one option, two option,or any combination of any
> of the choices.
>
> I use the base 2 methodology. For example if someone were to choose
> blue and orange I would sum the values 1+2=3. So 3 is the value which
> is equal to blue and orange.
>
> Or for example the stored value(31) is equal to 1+2+4+8+16, which is
> equal to the user choosing blue,orange,red,green,yellow.
>
> The storing of the data seems to be very effienct, but the problem is
> retreival during queries. It's very easy to find all entries that
> chose blue and orange b/c the mysql statement would be "Select * FROM
> table WHERE colors=3;".
>
> But I have no way to find all the choices that contain blue, which
> would mean, choosing blue alone, or with any combination of any of the
> other choices.
>
> PLEASE HELP!!!! I am really stuck
>

Couple of possibilities:

1. This is what happens when a student programmer thinks he knows how to set
up a database. Your method is clever, but wrong. Is this for a class, or
something you are just doing on your own for fun?

2. Another possibility- It looks like a devious professor is challenging
you to not stumble over what is simple programming logic- which you have
stubbed your toe on. If that's the case, here's a hint: 1 is the lonliest
number for a reason.

In any event, more info is needed. What are these the colors of? Wires?
Cars? It matters, because unless this is for something very out of the
ordinary, you will need to start over.

From:	hello
Subject:	Re: Multiple checkbox storage and retrieval
Date:	19 Jan 2005 12:50:23 -0800

>>Exactly right, except it's probably ([colors] & 4) on your system if
[]

THANK YOU!!!!

I can not tell you how much I apprecaite your help!!!! Thank you!!!!!

You are so great!!! You saved me so much time programming other
alternatives.

THANK YOU

I really think this is the most efficient way to do it.

THANK YOU!!!!

>>13 = 1101 (binary)
>>4 = 0100 (binary with a zero padded on)
>>13 & 4 = 1101 & 0100 = 0100

Good to know VERY USEFUL. THANK YOU!

>>Integrity considerations. See
>> http://www.dbdebunk.com/page/page/622161.htm
Also very helpful. Thank you.

From:	DA Morgan
Subject:	Re: Multiple checkbox storage and retrieval
Date:	Mon, 17 Jan 2005 21:00:12 -0800

stoppal@hotmail.com wrote:
> I have a form that I use collect data using checkboxes.
>
> example:
>
> Check all "colors" that apply:
> [] blue
> [] orange
> [] red
> [] green
> [] yellow
>
>
> The users can select one option, two option,or any combination of any
> of the choices.
>
> I use the base 2 methodology. For example if someone were to choose
> blue and orange I would sum the values 1+2=3. So 3 is the value which
> is equal to blue and orange.
>
> Or for example the stored value(31) is equal to 1+2+4+8+16, which is
> equal to the user choosing blue,orange,red,green,yellow.
>
> The storing of the data seems to be very effienct, but the problem is
> retreival during queries. It's very easy to find all entries that
> chose blue and orange b/c the mysql statement would be "Select * FROM
> table WHERE colors=3;".
>
> But I have no way to find all the choices that contain blue, which
> would mean, choosing blue alone, or with any combination of any of the
> other choices.
>
> PLEASE HELP!!!! I am really stuck

Two choices and you can choose between them:

Horizontal (assumes a finite list and no new colors will ever exist)
columns named person_id, blue, orange, red, green, yellow
you can select person_id or count or whatever where
color = blue

Vertical (assumes variable list of colors over time)
columns named person_id, color_id
you can select person_id or count or whatever where color = blue

But I agree with Alan ... either take a class or get a good book
on data normalization.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

From:	Ethan T
Subject:	Re: Multiple checkbox storage and retrieval
Date:	Tue, 18 Jan 2005 20:50:14 -0500

DA Morgan wrote:
> Two choices and you can choose between them:

I see nothing wrong with his idea, assuming your database has bitwise
operators ... just store the colors in a single integer variable, and if
blue has the 5th bit (2^(5-1) = 2^4 = 16), check for it by using
(`colors` & 16).

At least in MySQL, the '&' operator is simply a bit comparison, so that
query will return everything where the 5th bit is set.

> But I agree with Alan ... either take a class or get a good book
> on data normalization.

I'm no computer science student, but I'm curious what's wrong with the
bitwise approach.

--
eth'nT

From:	Gene Wirchenko
Subject:	Re: Multiple checkbox storage and retrieval
Date:	Wed, 19 Jan 2005 09:16:51 -0800

On Tue, 18 Jan 2005 20:50:14 -0500, Ethan T
wrote:

>DA Morgan wrote:
>> Two choices and you can choose between them:
>
>I see nothing wrong with his idea, assuming your database has bitwise
>operators ... just store the colors in a single integer variable, and if
>blue has the 5th bit (2^(5-1) = 2^4 = 16), check for it by using
>(`colors` & 16).
>
>At least in MySQL, the '&' operator is simply a bit comparison, so that
>query will return everything where the 5th bit is set.
>
>> But I agree with Alan ... either take a class or get a good book
>> on data normalization.
>
>I'm no computer science student, but I'm curious what's wrong with the
>bitwise approach.

Integrity considerations. See
http://www.dbdebunk.com/page/page/622161.htm

Sincerely,

Gene Wirchenko

From:	hello
Subject:	Re: Multiple checkbox storage and retrieval
Date:	19 Jan 2005 14:02:51 -0800

>>Integrity considerations. See
>> http://www.dbdebunk.com/page/page/622161.htm

I tried clicking on the links in the sentence below, but just got a
home page. Do you know how I can read the article?

First, read The Dangerous Illusion Parts 1(?) and 2(?) and see if that
gives you some ideas.

1)http://dmreview.com/?CFID=5315263&CFTOKEN=54967146?
2)http://dmreview.com/?CFID=5315263&CFTOKEN=54967146?

The article made some good points.

One point I'm mainly worried about is the size the bitmask can hold. I
doubt I'd have more then 100 items max , which would make a value of
........
2,535,301,200,456,460,000,000,000,000,000
If all items are included.

Would this be to big for a mysql database, and would it hinder
performance in a mysql database?

I am now swaying toward just having a joined/related table of the
items, but it would mean much more time spent programming. I'm not
worried about integity of the data,since I'm the only one using it, and
I know I'm not going to goof up the data. So any other items I should
consider, using a related table or bitmask?

Thanks again

From:	Ethan T
Subject:	Re: Multiple checkbox storage and retrieval
Date:	Wed, 19 Jan 2005 17:39:58 -0500

hello wrote:
> One point I'm mainly worried about is the size the bitmask can hold. I
> doubt I'd have more then 100 items max , which would make a value of

You definitely want to be careful using bitmasks on very large numbers.
It would probably be best to either:

a) separate the values into 8-bit or 16-bit chunks and store each chunk
as a numeric field

b) store the number as a string and parse it outside the SQL query (ugly)

c) store the binary as a string and parse based on a string
character-extracting function, sort-of like the base-10 solution you
mentioned earlier. SUBSTR([color], 4, 1) or whatever. This results in
larger strings than in (b), but I would expect it to be faster to process.

d) just go ahead and make a field for each

If your database supports booleans, I think (d) would be my favorite,
but I'm not an expert on how database performance is affected by these
"solutions."

--
eth'nT

From:	stoppal at hotmail.com
Subject:	Re: Multiple checkbox storage and retrieval
Date:	19 Jan 2005 07:36:44 -0800

>>I see nothing wrong with his idea, assuming your database has bitwise

>>operators ... just store the colors in a single integer variable, and
if
>>blue has the 5th bit (2^(5-1) = 2^4 = 16), check for it by using
>>(`colors` & 16).

Will this work the way I need it to, For example if I need to find all
records that have the color "red" which stores value "4" assumming the
below information.

Check all "colors" that apply:[colors]
[] blue
[] orange
[] red
[] green
[] yellow

If the user justed picked "red" and stored the number 4 in the field,
I know I can use "WHERE [colors]=4", to find it, But assuming the user
picked "red" and "green" then the stored value would be 4+8= 12. What
syntax would I use to search for it. For example, would l use "WHERE
('colors' & 4)". I've never seen that? how does this method work?
>>Contact a poster named Neo
How can I contact him?

Thanks

From:	Ethan T
Subject:	Re: Multiple checkbox storage and retrieval
Date:	Wed, 19 Jan 2005 13:53:22 -0500

stoppal@hotmail.com wrote:
> If the user justed picked "red" and stored the number 4 in the field,
> I know I can use "WHERE [colors]=4", to find it, But assuming the user
> picked "red" and "green" then the stored value would be 4+8= 12. What
> syntax would I use to search for it. For example, would l use "WHERE
> ('colors' & 4)". I've never seen that? how does this method work?

Exactly right, except it's probably ([colors] & 4) on your system if []
are the delimiters used for field names.

The single ampersand (&) means to do a bitwise AND on the two values.
Think of the bits themselves and it becomes clear. Take the value 13,
which means 1+4+8. 13 should match the pattern becauses it includes 4,
as shown below:

13 = 1101 (binary)
4 = 0100 (binary with a zero padded on)

13 & 4 = 1101 & 0100 = 0100

Thus, the result of 13 & 4 = 4. Since the value of the result is
non-zero, the WHERE clause will evaluate to true and the record is
returned. Now try 19 (1+2+16), which does not include a 4:

19 = 10011
4 = 00100

19 & 4 = 10011 & 00100 = 00000

Since the result is zero (19 & 4 = 0), it's false and the record is not
returned.

These cases make a distinction between a single ampersand and a double
ampersand (&&). Double ampersand means to compare the boolean value of
the two arguments and return a boolean value. If either argument is
nonzero (thus evaluates to true), the result is true, or often "1". A
single ampersand has a completely different meaning as explained above.

--
eth'nT

From:	stoppal at hotmail.com
Subject:	Re: Multiple checkbox storage and retrieval
Date:	18 Jan 2005 13:23:47 -0800

This is not for a class or school. I'm working on a website. My own,
for fun.

For my web site I am making a dynamic control panel, that the user can
add fields. These fields will be added to an input form, a search
form, and a search result page (ALL dynamically) The user will be able
to setup categories, within each category they can then setup the
fields, and within each field they can setup a variable(item lists) to
choose from. So the lists of options are dynamic. I used the colors
as an example of the items. But it could be anything, cars, yes/no,
planes, cables.

The list is dynamic (Vertical). I have a separate table per field
that stores the items in the field lookup.

What is the best way to store and retrieve the data, is there a better
way?

I was thinking of adding another table per field that would store the
choices made by the user, but I wanted to avoid making so many multiple
tables.
example [user_ID], [Choice]. so it would look like this
1,2
1,4,
1,8
1,16
2,8
2,32
etc.etc.

The otherway I thought I would do it was make one large table with all
the [users], [field] [choices].
user_id, field_ID, choice
1,1,4
1,1,8
1,1,16
2,1,8
2,1,32
etc. etc.

I have another idea that might work, I would use Base 10, and then
search based on the digit placement.

Example
Using Check all "colors" that apply:
[] whatever
[] this
[] that
[] theother
[] thing
In this case the data would be stored in a Boolean fashion.

1+10+100+1000+10000=11111 would represent the entire list of options.

Value 10001 would the last and first option, for example,
"whatever" and "thing".

So I might be able to search based on the value placement, but I'm
not sure of the "WHERE" clause.

Could I do WHERE colors="****1" , so it would find all values of
"whatever" I don't think "*" represent a wildcard, % does
not work b/c it represents any amount of digits.=. I need a
placeholder wildcard for this to work, but I'm just not familiar
enough with mysql code.

What would be most efficient way to store and retrieve the information?
I consider speed important.

Thank you.

From:	Alan
Subject:	Re: Multiple checkbox storage and retrieval
Date:	Tue, 18 Jan 2005 20:20:48 -0500

wrote in message
news:1106083427.865435.150720@c13g2000cwb.googlegroups.com...
> This is not for a class or school. I'm working on a website. My own,
> for fun.
>
> For my web site I am making a dynamic control panel, that the user can
> add fields. These fields will be added to an input form, a search
> form, and a search result page (ALL dynamically) The user will be able
> to setup categories, within each category they can then setup the
> fields, and within each field they can setup a variable(item lists) to
> choose from. So the lists of options are dynamic. I used the colors
> as an example of the items. But it could be anything, cars, yes/no,
> planes, cables.
>
> The list is dynamic (Vertical). I have a separate table per field
> that stores the items in the field lookup.
>
> What is the best way to store and retrieve the data, is there a better
> way?
>
> I was thinking of adding another table per field that would store the
> choices made by the user, but I wanted to avoid making so many multiple
> tables.
> example [user_ID], [Choice]. so it would look like this
> 1,2
> 1,4,
> 1,8
> 1,16
> 2,8
> 2,32
> etc.etc.
>
> The otherway I thought I would do it was make one large table with all
> the [users], [field] [choices].
> user_id, field_ID, choice
> 1,1,4
> 1,1,8
> 1,1,16
> 2,1,8
> 2,1,32
> etc. etc.
>
>
> I have another idea that might work, I would use Base 10, and then
> search based on the digit placement.
>
> Example
> Using Check all "colors" that apply:
> [] whatever
> [] this
> [] that
> [] theother
> [] thing
> In this case the data would be stored in a Boolean fashion.
>
> 1+10+100+1000+10000=11111 would represent the entire list of options.
>
> Value 10001 would the last and first option, for example,
> "whatever" and "thing".
>
> So I might be able to search based on the value placement, but I'm
> not sure of the "WHERE" clause.
>
> Could I do WHERE colors="****1" , so it would find all values of
> "whatever" I don't think "*" represent a wildcard, % does
> not work b/c it represents any amount of digits.=. I need a
> placeholder wildcard for this to work, but I'm just not familiar
> enough with mysql code.
>
>
> What would be most efficient way to store and retrieve the information?
> I consider speed important.
>
> Thank you.
>

Wow. I don't know where to begin. Working with what you have, you would need
to parse out the position of each digit, and then search for a digit in that
position. Not a big deal, really, just time consuming to build. Buit, in the
largest sense, what you want to do is not really optimally suited to a
relational database. Contact a poster named Neo. He may have just what you
need, and I think he is looking for something quasi-real to test it on.