当选择父类别时,将产品分配给子类别的最佳方法是什么?
。e如果我选择顶级类别,我希望获得所有子类别中的所有产品,而不仅仅是分配给所选当前级别的产品。
所以选择类别1将返回prod_id的1和2。选择类别2将返回prod_id 2。
**category**
id parent_id name
1 0 Vegetables
2 1 Tomatoes
3 2 Yellow Tomatoes
**product_category**
prod_id cat_id
1 1
2 3
**products**
prod_id name
1 Carrot
2 Small Yellow Tomato
这个查询是你想要的吗?
$query =<<<MYSQL
SELECT `prod_id`
FROM `produdct_category`
INNER JOIN `category` ON `product_category`.`cat_id` = `category`.`id`
WHERE `category`.`parent_id` = {$selectedParentId}
MYSQL;
更新问题,更新答案:
$query =<<<MYSQL
SELECT *
FROM `produdcts`
INNER JOIN `produdct_category` ON `products`.`prod_id` = `produdct_category`.`prod_id`
INNER JOIN `category` ON `product_category`.`cat_id` = `category`.`id`
WHERE `category`.`id` = {$selectedCatId}
OR `category`.`parent_id` = {$selectedCatId}
MYSQL;
更正表名并选择首先需要选择的字段。然后像这样做:
SELECT * FROM `products` AS p
INNER JOIN `product_category` AS pc ON p.id = pc.prod_id
INNER JOIN `category` AS c ON pc.cat_id = c.id
WHERE `parent_id` = YOUR_VALUE_HERE