在 PHP 中检索复杂类别结构下的产品时遇到问题
我希望你们能帮我解决这个问题。所以，我有一个产品表和一个类别表。下面列出了类别表的结构。大约有20个类别，深度为三到四级。为了获得类别树，编写了一个递归函数，并且运行良好。
我的新任务是显示给定类别的产品。作为示例，我将使用下面所示的数据。因此，如果我添加类别 id = 7 的产品，则必须在用户选择类别 7 时显示该产品，而且当用户选择类别 4 或类别 1 时也必须显示该产品。此外，如果用户选择 id = 1 的类别，它必须显示类别 = 1, 4, 5, 6 的产品。
我的类别表如下所示：
CREATE TABLE `categories` (
`category_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`category_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`category_slug` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`category_parent` smallint(5) unsigned NOT NULL DEFAULT '0',
`category_description_ro` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`category_description_en` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
下面是表中数据的示例：
category id | category name | category_parent
1 Categoria 1 0
2 Categoria 2 0
3 Categoria 3 0
4 Categoria 1.1 1
5 Categoria 1.2 1
6 Categoria 1.3 1
7 Categoria 1.1.2 4
I hope you guys can help me with this one. So, I have a products table and a categories table. The structure for the categories table is listed bellow. There will be about 20 categories with three or four levels deep. In order to get the category tree a wrote a recursive function and it is working fine.
My new task is to display products for a given category. As an example, I will use the data shown bellow. So, if I add a product with category_id = 7, I have to display that product when the user selects category 7, but also when the user will select category 4, or category 1. Also, if a user selects category with id = 1, it has to display products for category = 1, 4, 5, 6.
My categories table looks like this:
CREATE TABLE `categories` (
`category_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`category_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`category_slug` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`category_parent` smallint(5) unsigned NOT NULL DEFAULT '0',
`category_description_ro` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`category_description_en` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
Bellow is an example of data in the table:
category id | category name | category_parent
1 Categoria 1 0
2 Categoria 2 0
3 Categoria 3 0
4 Categoria 1.1 1
5 Categoria 1.2 1
6 Categoria 1.3 1
7 Categoria 1.1.2 4
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在类似的情况下，我创建了一个递归函数，它将获得一个类别的所有子级以及该类别本身。 中说
然后你可以简单地在 SQL
update
你想要一个函数 getChildren 来生成，例如 1，结果 [1,4,5,6,7]
那么你的查询是
这是一个简短的方法 所以这实际上是一个可以找到产品的语句
，即使它们只附加了一个类别 ID
in similar cases, I've made a recursive function that will get me all children of a category, and the category itself. Then you can simply say
in SQL
update
you want a function getChildren that yields, for, say 1, the result [1,4,5,6,7]
then your query is
which is a short way of writing
So this is really the statement that will find products even though they have exactly one category ID attached
这就是我的做法：
... WHEREcategory_id = n ORcategory_parent = n：选择当前类别（n是当前类别）及其第一级子类别。如果您想要更深入的层，则需要向下递归层。... WHERE Category_id = p：选择父类别（p是父 ID）。重复直到p为 0。This is how I would do it:
... WHERE category_id = n OR category_parent = n: select the current category (nis current) and it's first-level subcategories. If you want further layers deep, you'll need to recurse down the layers.... WHERE category_id = p: select parent category (pis parent id). Repeat untilpis 0.