Build multidimensional array from mysql database using php

Question

I have a theoretical question that I cannot seem to figure out. Imagine I have the following data in a database:

Main    Sub1   Sub2
a       x      y
x       t      u
u       f      g

I want to make a multidimensional array in PHP/mYSQL by essentially asking "what is each 'main' component made of?"

The result would be something like this:

Array
   (
   [0] => a
     (
     [0] => x
       (
       [0] => t
       [1] => u
       )
         (
         [0] => f
         [1] => g
         )
     [1] => y
     )
   )

My efforts result in lots of arrays, instead of a multidimensional array.

Ja͢ck · Accepted Answer · 2014-03-07 03:31:10Z

1

You can use references to solve this, although the results will get a bit messy:

$res = [];

foreach ($rows as $row) {
    // check if we have each sub component
    if (!isset($res[$row['sub1']])) {
        $res[$row['sub1']] = $row['sub1'];
    }
    if (!isset($res[$row['sub2']])) {
        $res[$row['sub2']] = $row['sub2'];
    }
    // build new component with references to the sub components
    $res[$row['main']] = [&$res[$row['sub1']], &$res[$row['sub2']]];
}

print_r($res);

Output

Array
(
    [x] => Array
        (
            [0] => t
            [1] => Array
                (
                    [0] => f
                    [1] => g
                )

        )

    [y] => y
    [a] => Array
        (
            [0] => Array
                (
                    [0] => t
                    [1] => Array
                        (
                            [0] => f
                            [1] => g
                        )

                )

            [1] => y
        )

    [t] => t
    [u] => Array
        (
            [0] => f
            [1] => g
        )

    [f] => f
    [g] => g
)

You can clean up the results by filtering out only the arrays:

print_r(array_filter($res, 'is_array'));

Output

Array
(
    [x] => Array
        (
            [0] => t
            [1] => Array
                (
                    [0] => f
                    [1] => g
                )

        )

    [a] => Array
        (
            [0] => Array
                (
                    [0] => t
                    [1] => Array
                        (
                            [0] => f
                            [1] => g
                        )

                )

            [1] => y
        )

    [u] => Array
        (
            [0] => f
            [1] => g
        )

)

edited Mar 7, 2014 at 3:31

answered Mar 3, 2014 at 2:15

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daniel Over a year ago

Do you mind including the database retrieval syntax (e.g., while ($row = mysql_fetch_assoc($result)) {) so I can understand your naming conventions? Thanks for your help!

Ja͢ck Over a year ago

@user1912319 The answer works on rows from a database in the same way.

Daniel Over a year ago

I am not understanding the $rows as $row and then checking if $row is set. Do you mind expanding a little?

Ja͢ck Over a year ago

@user1912319 I have added a few comments, hope it helps.

Collectives™ on Stack Overflow

Build multidimensional array from mysql database using php

1 Answer 1

Output

Output

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Output

Output

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related