How to display products with multiple categories in a table

Question

I have 3 tables namely product, category and product_category. How can I display products with multiple categories in a table for my admin panel?

  product table           category table        product_category table 
+---+------------+      +---+------------+    +-----------+------------+     
|id |    name    |      |id |    name    |    |product_id | category_id|
+---+------------+      +---+------------+    +-----------+------------+ 
| 1 | Cake       |      | 1 | Sweets     |    | 1         |     1      |
| 2 | Chocolate  |      | 2 | Dessert    |    | 1         |     2      |
+---+------------+      +---+------------+    | 2         |     1      |
                                              | 2         |     2      |
                                              +-----------+------------+

I tried creating a 'link table' but when I am trying to fetch the data, it gives me multiple rows.

$query = mysqli_query($con, "SELECT * FROM product, category, product_category WHERE product.product_id=product_category.product_id AND product_category.category_id=category.category_id");

It gives me this output:

+-------------+------------+  
|product name |  category  |
+-------------+------------+  
| Cake        |  Sweets    |
| Cake        |  Dessert   |
| and so on...             |
+-------------+------------+

but I want it to look like this:

+-------------+--------------------+  
|product name |  category          |
+-------------+--------------------+  
| Cake        |  Sweets,Dessert    |
| and so on...                     |
+-------------+--------------------+

here is a similar question: https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql — AppleJam, Jul 21 '19 at 12:34
Thank you @PaulSpiegel for the info. And also it solved my problem. Sorry I didn't notice that there's an similar question. Thank you — dyodoreu, Jul 21 '19 at 13:03

Kerkouch · Accepted Answer · 2019-07-21T12:44:44.550

1

You should use GROUP BY, GROUP_CONCAT and joins

SELECT product.id AS product_id,product.name,GROUP_CONCAT(category.name) AS category 
  FROM product 
  LEFT JOIN product_category ON product.id=product_category.product_id 
  INNER JOIN category ON category.id=product_category.category_id 
GROUP BY product_id;

edited Jul 21 '19 at 12:44

answered Jul 21 '19 at 12:31

Kerkouch

1,446
10
14

Thank you so much @Kerkouch . It solved my problem – dyodoreu Jul 21 '19 at 13:01

How to display products with multiple categories in a table

1 Answers1