60 checkboxes a simpler way, but how would I search

ade234uk

One the websites I am working uses a lot of checkboxes. 60 to be infact, so naturally I created a Table with 60 fields to begin with

The other night I was thinking, like you do that there is no need to have 60 fields, I could just combine all the results in to one.

When a checkbox is checked 1 is inserted
When a checkbox is unchecked 0 is inserted

When the form is posted all the 1 and 0's could be combined in to one string and then inserted in to one field like so 111100001010110001111100011

Then I could pull the results from the database and then pick off each number individually using the following code

// Split the string//
$checkbox_answers_temp = $row['checkbox_answers'];
$checkbox = explode(" ", $checkbox_answers_temp);

echo $checkbox[0];
echo $checkbox[1]; and so on.....

This would not only save a lot of Dbase space but it is easier to admin as well.

Then I came across my problem how would I able to search. If I had sixty fields this would be a peice of cake.

Any Ideas?

Piranha

Use an array instead:

<input type="checkbox" name="test[]">

Then when you recieve it you can just do a simple foreach or while:

$test = $_POST['test'];
foreach ($test as $one_test)
    echo "$one_test";

WDPEjoe

If you have it in one field, your mysql query could look like this to search that one field:

SELECT * FROM tbl WHERE SUBSTRING(checkboxCol, $checkBoxNumber, 1) = '1'

ade234uk

So it will check each number in the string and then end the loop once the last character has been outputted?

liquorvicar

Why do you think it's easier to have one column for storing all that data? If you have 60 bits of data about an item, use 60 columns. One of the golden rules for database use is normalisation. Basically, each column should contain one unique bit of information.

btw, 60 columns does seem a lot. Most tables I've seen that have that many columns are badly designed and could be redesigned using a lookup table.

ade234uk

When you say LookUp table what do you mean by this? I would like to explore all possibilites as it will teach me the proper way to design databases in the future.

Many Thanks for your help.

liquorvicar

It depends on what structure your data has, but, for example, let's say you have a table of products and a table of categories. Imagine a product could be in multiple categories. You could have a table like this:

product_id
product_name
category1
category2 etc

Better would be to break this up into 3 tables like this:

products
-----------
product_id
product_name

categories
-------------
category_id
category_name

product_category
----------------------
product_id
category_id

This basically creates a many-to-many relationship between your products and your categories. To query your data, you would the perform a JOIN on the products and the product_category tables (and possible the categories table as well depending on what info you need).