在php中查询分层mysql表

i have a table with 3 columns id,name and parent_id representing categories. The root categories(categories with no parents) have parent_id 0. All other categories have parent_id as the id of their immediate parent. There is no limit on the depth of categories i mean that a category can be 3,4 or even 10 levels down from a root category. What i need now is a PHP multi-dimensional array that contains all the root categories at the first level and then their immediate sub-categories at the next level,each sub-category under its parent category,and their sub-categories under them 1 level down. So its a tree like structure. there can be many levels in the tree

我有一个表有3列id，name和parent_id表示类别。根类别（没有父项的类别）具有parent_id 0.所有其他类别都将parent_id作为其直接父级的id。类别的深度没有限制我的意思是类别可以是从根类别下降3,4或甚至10个级别。我现在需要的是一个PHP多维数组，它包含第一级的所有根类别，然后是下一级别的直接子类别，其父类别下的每个子类别，以及它们下面的子类别1水平下降。所以它是一个像树一样的结构。树中可以有很多层次

I dont need the exact code but need an idea. one such idea is get all the root categories with a select query and then fire a select query for each root query to get its subcategoies and so on recursively but that would be too much select queries.

我不需要确切的代码但需要一个想法。一个这样的想法是使用选择查询获取所有根类别，然后为每个根查询触发一个选择查询以获得其子类，依此类推，但这将是太多的选择查询。

Or if i know that my table is going to contain say 300 rows at the maximum, how can i do something like

或者，如果我知道我的表最多会包含300行，我该怎么做呢

$categories=GetResultAsArray(select * from categories);

and now manipulate the $categories array in memory to get the desired tree.

现在操作内存中的$ categories数组以获得所需的树。

2 个解决方案

#1

You're right, using the solution with a "parentid" column is simple but it makes you write recursive queries. There are several other designs for storing hierarchical data in a database that let you do queries more efficiently.

你是对的，使用带有“parentid”列的解决方案很简单，但它会让你编写递归查询。还有其他几种用于在数据库中存储分层数据的设计，可以让您更有效地进行查询。

See:

看到：

• What is the most efficient/elegant way to parse a flat table into a tree?
• 将平台解析成树的最有效/优雅的方法是什么？
• My other answers to SO questions on hierarchical-data
• 我对层次数据的SO问题的其他答案
• My presentation Models for Hierarchical Data with SQL and PHP
• 我的演示文稿使用SQL和PHP的分层数据模型
• My book SQL Antipatterns: Avoiding the Pitfalls of Database Programming
• 我的书SQL Antipatterns：避免数据库编程的陷阱
• Sql Antipatterns Strike Back
• Sql Antipatterns Strike Back

#2

It's possible duplicate (http://*.com/questions/8431463/more-efficient-hierarchy-system/8431551) but here is a snippet to query an adjacent tree (parent_id,id,title):

它可能重复（http://*.com/questions/8431463/more-efficient-hierarchy-system/8431551），但这里有一个查询相邻树（parent_id，id，title）的片段：

$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>';