MySQL中的层次结构数据到PHP多维数组 [英] Hierarchy data in MySQL to PHP multidimensional array

问题描述

只是学习SQL，我想使用一个简单的父级(子级)来构建层次结构.就像堆栈溢出徽章(父母:问题徽章，孩子:利他主义者).

Just to learn SQL I wanted to make a hierarchy with a simple parent - child. Like the stack overflow badges (Parent: Question Badges, Child: Altruist).

这是我的SQL:

SELECT *
FROM (`badge_types`)
LEFT JOIN `badges` ON `badges`.`badge_type` = `badge_types`.`badge_type_id`

这就是我得到的:

(
    [0] => stdClass Object
        (
            [badge_type_id] => 2
            [badge_type_title] => Participation Badges
            [badge_type_description] => Badges earning by participating in various areas of the site.
            [badge_type_order] => 2
            [badge_id] => 1
            [badge_name] => Autobiographer
            [badge_level] => 3
            [badge_requirement] => Completed all user profile fields
            [badge_type] => 2
            [badge_order] => 1
            [badge_sites] => 0
        )

    [1] => stdClass Object
        (
            [badge_type_id] => 1
            [badge_type_title] => Experience Badges
            [badge_type_description] => Badges earned by amount of experience gain throughout the site.
            [badge_type_order] => 1
            [badge_id] => 2
            [badge_name] => Apprentice
            [badge_level] => 3
            [badge_requirement] => Achieved 500 experience
            [badge_type] => 1
            [badge_order] => 1
            [badge_sites] => 0
        )

)

如何将其转换为:

array(
    array(
        [badge_type_id] => 2
        [badge_type_title] => Participation Badges
        [badge_type_description] => Badges earning by participating in various areas of the site.
        [badge_type_order] => 2
        [badges] => array(
            array(
                [badge_id] => 1
                [badge_name] => Autobiographer
                [badge_level] => 3
                [badge_requirement] => Completed all user profile fields
                [badge_type] => 2
                [badge_order] => 1
                [badge_sites] => 0
            ),
            array(
                [badge_id] => 2
                [badge_name] => Example 2
                [badge_level] => 3
                [badge_requirement] => blah bla
                [badge_type] => 2
                [badge_order] => 1
                [badge_sites] => 0
            )
        )
    ),
    array(
        [badge_type_id] => 1
        [badge_type_title] => Experience Badges
        [badge_type_description] => Badges earned by amount of experience gain throughout the site.
        [badge_type_order] => 1
        [badges] => array(
            array(
                [badge_id] => 2
                [badge_name] => Apprentice
                [badge_level] => 3
                [badge_requirement] => Achieved 500 experience
                [badge_type] => 1
                [badge_order] => 1
                [badge_sites] => 0
            ),
            array(
                [badge_id] => 2
                [badge_name] => Example 2
                [badge_level] => 3
                [badge_requirement] => Achieved 1000 experience
                [badge_type] => 1
                [badge_order] => 1
                [badge_sites] => 0
            )
        )
    )
)

我可以对多个MySQL查询执行此操作，但理想情况下，我只想使用一个查询?

I can do it with multiple MySQL queries but ideally I just want to use one query if its possible?

推荐答案

使用SQL查询无法实现此目的，因为(由于关系模型的性质)SQL查询将始终返回平面"结果集而没有任何嵌套(罕见)供应商SQL扩展例外，其目标是生成XML输出等，但是MySQL没有此类扩展.

It is not possible to achieve this with SQL query, because (due to relational model nature) SQL query will always return "flat" resultset without any nesting (rare exceptions are vendor SQL extensions, targetted on generating XML output or alike, but MySQL has no such extensions).

如果要从SQL结果集中获取嵌套数组，则必须使用PHP代码对其进行后处理.可以像在数组(用SQL进行预排序)上的分组循环那样组织此代码，每次内部"表的键更改时就开始新的组.这可以用PHP相当通用的方式完成，因此您可以编写一个这样的函数来对许多SQL查询进行后处理(为其提供适当的参数).

If you want to get nested arrays from SQL resultset, you have to postprocess it with PHP code. This code may be organized like grouping loop over an array (presorted with SQL), starting new group every time a key of "inner" table changes. This can be done in PHP in rather universal way, so you can write one such funciton to postprocess many SQL queries (giving it appropriate parameters).

添加是这样的功能:

function groupnest( $data, $groupkey, $nestname, $innerkey ) {
  $outer0 = array();
  $group = array(); $nested = array();

  foreach( $data as $row ) {
    $outer = array();
    while( list($k,$v) = each($row) ) {
      if( $k==$innerkey ) break;
      $outer[$k] = $v;
    }

    $inner = array( $innerkey => $v );
    while( list($k,$v) = each($row) ) {
      if( $k==$innerkey ) break;
      $inner[$k] = $v;
    }

    if( count($outer0) and $outer[$groupkey]!=$outer0[$groupkey] ) {
      $outer0[$nestname] = $group;
      $nested[] = $outer0;
      $group = array();
    }
    $outer0 = $outer;

    $group[] = $inner;
  }
  $outer[$nestname] = $group;
  $nested[] = $outer;

  return $nested;
}

data是要嵌套的数组(SQL结果集)，

data is array (SQL resultset) to be nested,

groupkey是外部"实体主键的列名

groupkey is column name of the 'outer' entity primary key,

nestname是将放置内部行"的字段的名称，

nestname is name of the field into which 'inner rows' will be put,

innerkey是内部"实体主键的列名.

innerkey is column name of the 'inner' entity primary key.

在结果集中，外部"实体的所有列都必须位于$innerkey列之前，而内部"实体的所有列都必须位于其后.

In the resultset all columns of 'outer' entity must preceed $innerkey column and all columns of 'inner' entity must follow it.

要正确分组，结果集必须首先由外部"实体(如order by badge_type_title, badge_type_id, ...)中的 unique 表达式排序. order by中的后面字段将定义内部"组中的顺序.

To be grouped correctly, resultset must be firstly ordered by a unique expression from 'outer' entity, like order by badge_type_title, badge_type_id, .... Later fields in order by will define ordering inside 'inner' groups.

要嵌套3个或更多实体的联接，您可以多次使用此功能(从内向外折叠")

To nest join of 3 or more entities you can use this function several times (folding "from inside to outside")

这篇关于MySQL中的层次结构数据到PHP多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！