MySQL search by special ID

Question

I have item and a category for this item. Category have an ID. I need to put category ID for item in DB, but item can have 2,3 or more categories. And what I need to do?

In items table make a new cell name "category" and put categories id with comma (7,4,8...)? If this way then how to search by category in PHP?

Answer 1

If I am understanding your problem you have an item that can have multiple categories. If that is the case, you do not store the multiple categories in the same column.

You would want to set up your tables in this manner:

create table item
(
  id int,  (PK)
  name varchar(10)
);

create table category
(
  id int,  (PK)
  name varchar(10)
);

create table item_category
(
  item_id int,  (FK)
  category_id int  (FK)
);

This would allow you to have your items and categories in separate tables and then use a join table to allow your item to have multiple categories.

If you had the following sample data for the tables above:

insert into item values
  (1, 'item 1'), 
  (2, 'item 2');

insert into category values
  (1, 'category 1'), 
  (2, 'category 2');

insert into item_category values
  (1, 1), 
  (2, 1),
  (2, 2);

Then your query would be similar to this:

select i.id item_id,
  i.name ItemName,
  c.id CategoryId,
  c.name CategoryName
from item i
left join item_category ic
  on i.id = ic.item_id
left join category c
  on ic.category_id = c.id

See SQL Fiddle with Demo. Which gives the result:

| ITEM_ID | ITEMNAME | CATEGORYID | CATEGORYNAME |
--------------------------------------------------
|       1 |   item 1 |          1 |   category 1 |
|       2 |   item 2 |          1 |   category 1 |
|       2 |   item 2 |          2 |   category 2 |