PHP MySQL item categories

I'm trying to display a list of items with their name and开发者_如何学Python all categories they belong to.

My database structure is:

Items table with Id, Item_Name.
Categories table with Id, Category_Name
Items_Categories table with Id, Item_id, Category_id.

I'd like to display the results in a table with the Item Name, and a comma-delimited list of its Categories' Names. I'm not sure how to do this with a single query. Thanks in advance.

You need to use the GROUP_CONCAT function that is available in mysql:

select i.item_name, group_concat(c.category_name) as categories
from items i
inner join items_categories ic on i.id = ic.item_id
inner join categories c on ic.category_id = c.id
group by i.item_name

I won't write it for you, but you need to use group_concat() function (MySQL).

I must say I did not test this query, but it should be something similar to the following:

SELECT i.Id, i.Item_name, GROUP_CONCAT(c.Category_Name) AS category_list
FROM Items i
JOIN Items_categories ci ON ci.Item_id = i.Id
LEFT JOIN Categories c ON c.Id = ci.Id
GROUP BY i.Id

Note that you can use GROUP_CONCAT to create a comma seperated list.

SELECT i.Item_Name, GROUP_CONCAT(c.Category_Name) AS Category_List
FROM Items AS i
LEFT OUTER JOIN (
  Items_Categories AS ic 
  INNER JOIN Categories AS c ON ic.Category_id = c.Id
) ON i.Id = ic.Item_id
GROUP BY i.Id;