One product, mutiple categories...problem!

Hi everyone,

I've looked for an answer to my problem here, and those I have found I don't quite understand...my query is this:

Scenario: I have a client who is selling food items via her website. She has many items that need to be listed under more than one category...for example Product '1' needs to be listed under Categories 'A' and 'B' whilst Product '2' needs to be listed under Categories 'C' and 'D'.

I have the products insert page set-up to allow her to list Product '1' under Category 'A' (from the database category table), but I can't figure out how to allow her to list Product '1' under Category 'B' aswell.

I seem to be unable to create a multiple selection of categories and allow her to input ALL her selections into the product category field in the product field and then display it on the front end of the site...

Can anyone please help me with a laymans answer??

Many thanks in advance,

Martin

its difficult to give a definitive answer without knowing your current database structure, but here is my approach.
Hope it is clear

When you have one category per item, you can store the categoryid as a field in the items table, and everything is straightforward.
With this structure, you can only have one category per item, (unless you duplicate the item).

To have multiple categories per item, you need a separate table with a one-to-many relationship with the items table, with itemid as the foreign key.
(You could add a primary key to this table, or create a compound key of the ItemID and CategoryID fields)

so your table could look like..

ItemID : CategoryID
1 : 1
1 : 2
2 : 1

Linked to the items table, this would mean item 1 belongs to both categories one and two. Item 2 belongs only to category one

When inserting a record, your form would need to show all categories available, using either a multi select list or check boxes, and have a multiple record insert behavior acting on this category table.

Thanks, Craig, much appreciated. Will try that out.
Martin