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