如何获得类别树而不循环查询 - How to get categories tree without looping queries?

我有这样的查询:

$query='select id, bg_category from products_categories where 
visible="1" and parent="0" group by bg_category order by bg_category ASC';

你可以想象从这里开始，我循环$num_rows到子类别，然后到它的子类别。不管怎么说，它是有效的，因为类别的数量不到20个。但是现在类别超过130个，这种生成类别树的方式浪费了服务器资源。我现在正在考虑改变这一点，并寻找php解决方案，将只执行1个查询。我在这里发现:在PHP中从平面数组构建树，但无法理解/使其工作:

$q = mysql_query("SELECT id, parent_id, name FROM categories");
while ($r = mysql_fetch_row($q)) {
$names[$r[0]] = $r[2];
$children[$r[0]][] = $r[1];
}
function render_select($root=0, $level=-1) {
global $names, $children;
if ($root != 0)
echo '<option>' . strrep(' ', $level) . $names[$root] . '</option>';
foreach ($children[$root] as $child)
render_select($child, $level+1);
}
echo '<select>';
render_select();
echo '</select>';

这上面应该是如何工作的?我需要通过render_select();

如果这个解决方案工作，它会节省服务器资源而不是循环查询吗?提前感谢您的帮助

编辑:我的数据库具有以下类别结构:

#id        #bg_category      #parent
1          electronics         0
2          Phones              1
3          Smartphones         2
4          normalphones        2

输出如下:

<ul>
<li>electronics
<ul class="sublevel">
<li>Phones
<ul><li>Smartphones</li>
</ul></li></ul></li>
</ul>

等. .希望对大家有所帮助

如果它总是只有2层深，你可能可以使用下面的查询。在php中，您可以检查当前行的bg_category是否与前一行相同，以显示或不显示该列，与child1相同，因为它们可以有多个child2s。

为了说明，我不按顺序添加了一些额外的类别和子类别。

我使用3个下划线表示1层深度，6个下划线表示2层深度，但您实际上可以将一些html标签放入sql中，以便查询为您生成您将在每个级别上放置的内容。

不管数据库中行的顺序如何——由id字段决定——这应该都能工作，但就像我说的，它只能工作2层深度。

虽然看起来很糟糕，但您可以设置一些视图来简化实际运行的查询。当然，这仍然假设你只深入2个关卡。如果你有子类别的子类别，sql可能不是最好的选择。

小提琴:http://sqlfiddle.com/!2/630c6/18/0

select w.bg_category, w.child, x.child as child2
from(
select x.bg_category, y.bg_category as child
from(
select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)
  ) x
join
(
  select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)
  ) y on x.id = y.parent
  ) w
left join
(
select x.bg_category, y.bg_category as child
from(
select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)
  ) x
join
(
  select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)
  ) y on x.id = y.parent
  ) x on w.child = x.bg_category
where substr(w.bg_category,1,1) <> '_'