Hierarchy data in MySQL to PHP multidimensional array

Question

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?

Using number of SQL queries proportional to amoung of selected data is very bad practice! You should use your single query and adapt its resilts to your needs. — mas.morozov
– mas.morozov, Commented Oct 28, 2013 at 8:37
I was hoping for a php solution, maybe I should edit my question — Adam
– Adam, Commented Oct 28, 2013 at 10:21
Ok, I will edit my answer and give my PHP solution full code — mas.morozov
– mas.morozov, Commented Oct 28, 2013 at 10:24
Added example of possible PHP solution. And here you can test it online - codepad.org/EqlkVYXV — mas.morozov
– mas.morozov, Commented Oct 28, 2013 at 12:04

mas.morozov · Accepted Answer · 2013-10-28 14:23:57Z

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

Added example of possible PHP solution. And here you can test it online - codepad.org/EqlkVYXV

Collectives™ on Stack Overflow

Hierarchy data in MySQL to PHP multidimensional array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related