Multiple Categories for 1 Item ????

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dsiguy
Forum Newbie
Posts: 2
Joined: Fri Mar 21, 2003 1:38 pm

Multiple Categories for 1 Item ????

Post by dsiguy »

I currently have the following setup.

Databases

Code: Select all

Categories 
Catid   Cat Name 
1         Small 
2         Medium 
3         Large
----------------
I want to add these categories
4         Red 
5         Blue 
6         Poly 
7         Cotton 

Items 
Item      Color      Size       Fabric    CatID 
Shirt1     Red       Small      Cotton     1 
Shirt2     Red       Medium     Cotton     2 
Shirt3     Red       Large      Cotton     3 
Shirt4     Blue      Small      Poly       1 
Shirt5     Blue      Medium     Poly       2 
Shirt6     Blue      Large      Poly       3

I am curruntly able to select a category and show the details with no problems. But I cannot make an item have more than one category. I would like to be able to select 1-7 (category) and give me the proper result.

Any suggestions would be appriciated.
craigh
Forum Newbie
Posts: 19
Joined: Sun May 19, 2002 2:50 pm

Post by craigh »

So what you are doing is trying to get a many-to-many relationship between the two tables. In order to do this, you need third table.

Code: Select all

categories
cat_id INT
cat_name varchar

items
item_id INT
item_desc varchar

items_categories
item_id
cat_id
Then, this third table show the relationship between the two tables, so you may have the follwoing entries:

Code: Select all

cat_id  cat_name  
1         Small  
2         Medium  
3         Large 
4         Red  
5         Blue  
6         Poly  
7         Cotton

items
item_id   item_desc
1           Shirt1
2           Shirt2
3           Shirt3
4           Shirt4

items_categories
item_id     cat_id
1              4
1              1
1              7
2              4
2              2
2              7
etc...
so then with any one item, you select like so:

Code: Select all

select * from items left join items_categories on items.item_id=items_categories.item_id left join categories on items_categories.cat_id=categories.cat_id where items.item_id=1;
which should return three rows with the three categories for that item.

whew!

:-)
dsiguy
Forum Newbie
Posts: 2
Joined: Fri Mar 21, 2003 1:38 pm

Post by dsiguy »

Wow,

I never would have thought of that but that totaly makes sense.

I do have one question about your reply.

You showed me the code to use and I am a little bit puzzeled.I am very new to php and mysql. I have been able to put together a site that lists categories and once you click it takes you to a list of Products.

The code you gave me what does each part do?
How would I print on the screen the categories?

Thanks
craigh
Forum Newbie
Posts: 19
Joined: Sun May 19, 2002 2:50 pm

Post by craigh »

dsiguy wrote: I never would have thought of that but that totaly makes sense.
:-) Well, that is called normalization and it makes sense because someone else (much smarter than me) figured it out a long time ago.
dsiguy wrote: The code you gave me what does each part do?
complicated to explain. Can you be more specific?

This one (shown above) will pull all the categories for a selected item (in this case, item 1):

Code: Select all

select * from items left join items_categories on items.item_id=items_categories.item_id left join categories on items_categories.cat_id=categories.cat_id where items.item_id=1;
This one will pull all the products for a selected category (in this case, category 1):

Code: Select all

select * from items left join items_categories on items.item_id=items_categories.item_id left join categories on items_categories.cat_id=categories.cat_id where categories.cat_id=1;
How would I print on the screen the categories?
pulling all the categories is simple:

Code: Select all

select * from categories;
help?
Post Reply