从MySQL结果创建分层数组

I've seen other questions about this but not quite like my situation. I have the following table in MySQL:

我已经看到了关于这个的其他问题，但不太像我的情况。我在MySQL中有以下表格：

   term_id       name           slug                 taxonomy     parent
1             Entry Form     entry-form           format       0
2             Page           page                 format       3
3             Facebook       facebook             format       0
4             Entry Form     facebook-entry-form  format       3
5             Twitter        twitter              format       0
6             Single        single                format       2

I have the following OBJECT query:

我有以下OBJECT查询：

$formats = $wpdb->get_results($wpdb->prepare("
    SELECT * FROM table t
    WHERE t.taxonomy = 'format'
"));

I wind up with the following array:

我结束了以下数组：

Array ( [0] => stdClass Object ( [term_id] => 1 [name] => Entry Form [slug] => entry-form [taxonomy] => format [parent] => 0 ) [2] => stdClass Object ( [term_id] => 2 [name] => Page [slug] => page [taxonomy] => format [parent] => 3 ) [3] => stdClass Object ( [term_id] => 3 [name] => Facebook [slug] => facebook [taxonomy] => format [parent] => 0 ) [4] => stdClass Object ( [term_id] => 4 [name] => Entry Form [slug] => entry-form-facebook [taxonomy] => format [parent] => 3 ) [5] => stdClass Object ( [term_id] => 5 [name] => Twitter [slug] => twitter [taxonomy] => format [parent] => 0 ) [6] => stdClass Object ( [term_id] => 6 [name] => Single [slug] => single [taxonomy] => format [parent] => 2 ) ) 1

All of the above needs to be turned into a hierarchical list on output that looks like this:

所有上述内容都需要转换为输出上的分层列表，如下所示：

Entry Form
Twitter
Facebook
 - Entry Form
 - Page
 -- Single

As such, I need to turn array $formats into a hierarchical array based on the parent field. A parent of 0 means it is a top level item. As such, since Single has parent of 2 it is the child of Page which in turn has parent of 3 and is a child of Facebook.

因此，我需要根据父字段将数组$格式转换为分层数组。父级为0表示它是*项目。因此，由于Single有2的父亲，所以Page的子女又是3的父母并且是Facebook的孩子。

Can anyone help me turn my array into a hierarchical array and then show me how I can loop through it for output?

任何人都可以帮助我将我的数组转换为分层数组，然后告诉我如何循环输出它？

2 个解决方案

#1

If performance due to volume of queries is not going to be a problem, the simplest solution is that, instead of doing a single query that populates an array, you do one query per node in your hierarchical tree, adding a "AND parent = $id", where $id is the term_id of the current node. Something like:

如果由于查询量导致的性能不会成为问题，最简单的解决方案是，不是进行填充数组的单个查询，而是在分层树中为每个节点执行一次查询，添加“AND parent = $ id“，其中$ id是当前节点的term_id。就像是：

Do a SELECT WHERE .... AND parent = 0;
做一个SELECT WHERE .... AND parent = 0;
for each result in 1, $id = term_id, do a select WHERE ... AND parent = $id
对于1中的每个结果，$ id = term_id，选择WHERE ... AND parent = $ id
Repeat recursively until no more results
递归重复，直到没有结果

If performance is a problem you can still dump the query to your array and apply the same algorithm to the array, but you most likely will have memory issues doing it that way if you really have that much volume.

如果性能是一个问题，您仍然可以将查询转储到您的阵列并将相同的算法应用于阵列，但如果您确实拥有那么多的音量，那么您很可能会遇到内存问题。

#2

You need to put the data into assoc array while fetching it from the database:

您需要在从数据库中获取数据时将数据放入assoc数组中：

//$groups - result array

$groups = array();

//$record contains the assoc array of the current record
while($record = $result->fetchAssoc()) {
      if (!isset($groups[$record["parent"]]))
      {
            $groups[$record["parent"]] = array();
      }

      array_push($groups[$record["parent"]], $record);
}

In the end you will get an assoc array of hierarchy with parent as a key. Then traverse through it recursively, and you'll get the result:

最后，您将获得一个层次结构的关联数组，其中父项作为键。然后递归遍历它，你会得到结果：

function print_recursively(&$groups, $parent_id, $dashes = '')
{
    if(isset($groups[$parent_id]))
    {
          foreach($groups[$parent_id] as $key => $value)
          {
                print $dashes . ' ' . $value["name"];
                print_recursively(&$groups, $value["term_id"], $dashes . '-');
          }
    }
}

I didn't test this code, but the algorithm is correct.

我没有测试这段代码，但算法是正确的。

#1

Do a SELECT WHERE .... AND parent = 0;
做一个SELECT WHERE .... AND parent = 0;
for each result in 1, $id = term_id, do a select WHERE ... AND parent = $id
对于1中的每个结果，$ id = term_id，选择WHERE ... AND parent = $ id
Repeat recursively until no more results
递归重复，直到没有结果

#2