| id | name | category |
|---|---|---|
| 1 | shoes | Nike, Adidas, Jordan |
How can I have multiple categories for "shoes" like the example up there. It doesn't mean to be separated with commas. The table has to be in MySQL
Asked
Active
Viewed 117 times
2 Answers
0
You can make a mapping table with categories and products like each product id can be associated with multiple category ids and then at view time use MySQL group_concat function to get comma separated category names.
| ProductID | Category Id |
|---|---|
| 1 | 1 |
| 1 | 2 |
Use query as
Select products.title,group_concat(categories.title) as product_categories from products inner join
product_categories on products.id=product_categories.product_id
inner join categories on product_categories.category_id=categories.id
group by products.id
Ahmed Memon
- 120
- 5
-
Hey Ahmed Memon and how exactly can I fetch this data so I can use it – Apr 18 '21 at 01:45
-
$result=$conn->query($sql); while ($row=$result->fetch_assoc()) { } This one is not working – Apr 18 '21 at 01:49
-
Kindly mention the error if there is any. – Ahmed Memon Apr 18 '21 at 22:40
-
"Undefined array key" when I'm trying to fetch the title of product_categories – Apr 19 '21 at 02:02
-
I have updated the answer use product_categories as a key to getting the product categories from the updated query. – Ahmed Memon Apr 19 '21 at 13:19
0
I believe you are looking for many to many relationship.
You have to create 3 new tables,
Products(pid,...)
Categories(cid,...) and
Product_Category(pid,cid,..)
Where pid is a foreign key to Products table and category_id is a foreign key to Categories table.
Dharman
- 30,962
- 25
- 85
- 135
Nikhil Joy
- 1
- 1
- 7
-
The code @ahmed-memon wrote up there works but how can I fetch it tho. $result=$conn->query($sql); while ($row=$result->fetch_assoc()) { } This isn't working – Apr 18 '21 at 01:53