More than one category

[Fargoth]

Hi,

In mySQL I have a table for categories with id, name, parentid and then I have a table for items with id etc.... and categoryid. And this works until I realised that some items fall into 2 or 3 categories.

At the moment the category of an item is an integer of the id in the categories table, so how can I change this to have more than 1 category for an item?

Thanks

[leatherback]

Hi,

You can fix this by making a ïn-between" table.

Your tables:

CATEGORIES
Id
Name
ParentID

ITEMS
ID
...

Create:

CATE_TO_ITEM
CatNr
ItemNr

So fill this table like this:

CatNr ItemNr
1 1
2 2
3 2
3 3
2 1

Query like:
Select * from CATEGORIES, CATE_TO_ITEM, ITEMS where ((CATEGORIES.ID eq CATE_TO_ITEM.CatId) and (ITEMS.ID eq CATE_TO_ITEM.itemNr) and ITEMS.ID eq 1)

Hope this helps