按类别列出项目 [英] List items by category

问题描述

我有3张桌子:

类别doesnt matter

item2cat:itemID|catID

item2cat: itemID|catID

项目:id|name|desc

我想列出给定类别中的项目，但我不知道如何以简单的方式进行. (使用PHP和MySQL)

I want to list the items that are in the given category, but I don't know how to do it in a simple way. (With PHP and MySQL)

我需要这种表结构，因为我希望一个项目有多个类别.

I need this table-structure because I want multiple categories to one item.

推荐答案

您可能想加入 item表中的item2cat表:

You probably want to join the item2cat table on your item table:

SELECT
  item.id
, item.name
, item.desc
FROM item
INNER JOIN item2cat ON item.id = item2cat.itemID
WHERE item2cat.catID = [category_id]

或者例如

SELECT
  item.id
, item.name
, item.desc
, category.id
, category.name
FROM item
INNER JOIN item2cat ON item.id = item2cat.itemID
INNER JOIN category ON item2cat.catID = category.id
WHERE category.id IN ( [category_id], [category_id], [category_id])

更新
如果您这样更改表ID:

UPDATE
If you changed your table IDs like this:

item (itemId, name, desc)
item2cat (itemId, categoryId)
category (categoryId, name, etc)

您可以像这样重写第一个查询:

you could rewrite the first query like:

SELECT
  item.itemId
, item.name
, item.desc
FROM item
INNER JOIN item2cat USING( itemId )
WHERE item2cat.categoryId = [category_id]

另一个优点是，每个表的id列现在都是明确的.

Another advantage is that the id column of every table is now unambiguous.

这篇关于按类别列出项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！