按类别列出项目 [英] 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屋!
查看全文