1

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...                     |
+-------------+--------------------+  
dyodoreu
  • 13
  • 5
  • 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

1 Answers1

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;
Kerkouch
  • 1,446
  • 10
  • 14