php - 列出嵌套类别的产品

Question

我为我的项目工作分层类别和产品表。

类别表结构:

ID - parent - 职务

1 - 0 - 电脑

1 - 0 - 电话

...

第 25107 行的类别表计数。

产品表结构

ID - CAT - 标题

1 - 1 - 产品名称在这里

我想要列表产品的当前类别和子类别。

echo $subcats = categoryChild(0);

输出是:64198,18355,18356,95623,90504,6118,90505,6117,90506,90507,6119,6120,90508,90509,90510,90511,6103,90512,90513,90515,19265 142688,142790,90516,6105,90517,90518,90519,90520,90521,91691,91692,92189,92190,92312,92313,95618,95619,95620,95621,95622,142684,142690,142692,142694,142696, 142698,142700,3596

我正在使用这个输出 MySQL IN 语句。最后生成这个 SQL 查询:

SELECT * FROM products WHERE cat IN (64198,18355,18356,95623,90504,6118,90505,6117,90506,90507,6119,6120,90508,90509,90510,90511,6103,90512,90513,90514,90515,142686,142688,142790,90516,6105,90517,90518,90519,90520,90521,91691,91692,92189,92190,92312,92313,95618,95619,95620,95621,95622,142684,142690,142692,142694,142696,142698,142700,3596)

这个查询很慢...请帮助我。

Answer 1

试试这个:

select p.id as id, p.title as title, c.title as category, c.id as catid
from products p JOIN
(select * from category where id = 1 OR parent = 1) c
on p.cat = c.id

看看它是如何在 SQLFIDDLE 上工作的

要列出所有层次结构级别，请编写如下代码:

// return all childs of category with id $cat
function list_childs($cat){
    // run above query and change (select * from category where id = 1 OR parent = 1)
    // to (select * from category where parent = $cat) and return results
}

然后在您的代码中为您想要获取 child 的每个类别调用此函数