Hierarchy data in MySQL to PHP multidimensional array

De openkb
Aller à : Navigation, rechercher

Sommaire

Questions

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).

This is my sql:

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

And here is what I get:

(
    [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
        )

)

How can I turn this into:

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
            )
        )
    )
)

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

Answers

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).

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).

  ADDITION    Here is such a function:
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 is array (SQL resultset) to be nested,

groupkey is column name of the outer entity primary key,

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

innerkey is column name of the inner entity primary key.

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

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.

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

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/19604447/hierarchy-data-in-mysql-to-php-multidimensional-array

Related

Outils personnels
Espaces de noms

Variantes
Actions
Navigation
Outils