Multiple options inserted into a database against a single item

[toyfruit]

Hi All,

I'm new to php and would like to create a list of tickbox colours which once selected can be entered into a database field against a single object. I then want a user to be able to search for items in the database that contain one or more of the selected colours. So for example:

 

BACKEND:

My tickbox colours might be:

Blue, Red, Black, Grey, Green.

 

I then want to associate say Blue, Red, Black with an item - say item1.

 

Item2 may have just Black associated with it (each item can have 1 to all of the tickbox colours associated with it).

 

FRONTEND:

A user can enter the site and will be presented with the same tickbox colours. When they have made their selection of one or more colours, there will be a search of the database for any item containing any of the colours they have selected.

 

MY QUESTIONS:

1. How can I store in the database the fact that there might be 1 or many colours against a single item? I know how to store single pieces of information against an item, but not how to store a variable amount.

2. How can I search the database for items that have one or more of the colours associated with them?

 

Any help would be greatly appreciated as I can see this is something I will need to apply in many different scenarios.

[GingerRobot]

What you don't do is think of storing the multiple values in one field. If you do, you'll run into all sorts of problems. The correct approach is to use a separate table. For example, you might have the following items table:

 

itemid, description, price

 

You would then have a separate colours table:

 

itemid, colour

 

There would then be a record in the colours table for each individual colour for each item. As for the searching of multiple tables, ill direct you to an excellent tutorial on this: http://www.phpfreaks.com/tutorial/data-joins-unions. The tutorial will also help you to understand why you need multiple tables. For further reading, google database normalization.

[toyfruit]

Cool thanks - I think I understand