php - 如何在表格中显示具有多个类别的产品

Question

我有 3 个表，即产品、类别和产品类别。如何在管理面板的表格中显示具有多个类别的产品？

  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      |
                                              +-----------+------------+

我尝试创建一个“链接表”，但是当我尝试获取数据时，它给了我多行。

$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");

它给了我这个输出：

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

但我希望它看起来像这样：

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

标签： phpmysqlcategoriesproduct

Answer 1

您应该使用GROUP BY,GROUP_CONCAT并加入

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;