best way to store info from checkboxes

mistafeesh

I am building a directory of local businesses. As well as the bog standard directory info, there are 18 checkboxes for them to tick for the category of their business. The complicated bit (for me!) is that they can tick multiple boxes...

I need to be able to display lists based on category or area, and pages on specific companies which show their categories.

The two ways I can think of doing it, are: (1) a field for each category, but this would bloat the database, and make coding the pages a headache, as I'd have to repeat everything.
(2) a few fields for 1st, second, third, fourth category. This would mean I could code the pages with less faff, but I'd still have to duplicate the code for each field. And I'd have to limit the number of categories people could go for...

anyone with more PHP/MySQL nouse got any better ideas?

paulnaj

Hi there.

In the end, you have to store 18 bits of info, regardless of how you implement it.

But you could store it as one 18-char string of zeroes and ones.
This could be passed from page to page or stored in a db quite easily.

ahundiak

You have a many-to-many relationship between business and category. Therefore you need a third table to link the two.

CREATE TABLE business_category
(
business_id INTEGER,
category_id INTEGER
);
This article might help:
http://www.phpbuilder.com/columns/barry20000731.php3

mistafeesh

thanks guys!

a very helpful article, ahundiak. I thought there must be a more sensible way of doing it!!😃

I'm interested in the idea of storing the whole lot in one string though. I could have one table with a list of the categories, and an id (maybe a letter rather than a number, cos then it can be one digit) a la that Barry Wise article, and then store the selected categories in a long field.

Only thing is, how would I do that??

I don't know what kind of PHP/MySQL commands would allow me to store multiple bits of info in one field and then retrieve them...

I guess my noob roots are showing through!

ahundiak

As paulnaj suggested, you could just store a string with each position representing a different category. But it's going to be a real pain to do queries like" all business's for a given set of categories".

I would strongly suggest that you follow standard sql practices and use a relation table. It will make life much easier.

mistafeesh

I think I will.

Danicek

Using something like 01000101001 is probably good way how to reduce db size and number of collumns/tables, however it is really wrong way if we will talk about db design.
So this way you will get large number of columns/tables, however retrieving data will be much easier.