Creating multi-dimensional array from DB

Question

Hi

I really hope someone can help me with this. I'm struggling with it for nearly two days now...

I have a DB-table "Device" and a table "Connection". I'm using it to visualize my company's network. To pass the data to the JS-framework I use to visualize the data I need an array like this:

Array
(
    [id] => 1
    [name] => TestPC1
    [children] => Array
        (
            [0] => Array
                (
                    [id] => 2
                    [name] => Testhub 2
                    [data] => Array
                        (
                        )

                    [children] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 3
                                    [name] => Rack3
                                    [data] => Array
                                        (
                                        )

                                    [children] => Array
                                        (
                                        )

                                )

                            [1] => Array
                                (
                                    [id] => 4
                                    [name] => Rack4
                                    [data] => Array
                                        (
                                        )

                                    [children] => Array
                                        (
                                        )

                                )

                        )

                )

        )

)

The device-table looks like this:

A     |     B
-------------------
1     |     2
2     |     3
2     |     4

The visualization of this example looks like this:

http://img34.imageshack.us/img34/4230/netmd.jpg

Does anyone have an idea, how to get from the db-data to this array?

Thank you.

Answer 1

I suggest you browse (both this site and internet in general) for "how to represent trees in RDBMS".

Start here, maybe.

Depending on how many "trees" you have to manage, and the maximum depth of your hierarchy, different approaches can be more or less feasible.

Answer 2

Let's say you want to do it in PHP using that table, for example's sake I'll use an array to represent the returned database data after a JOIN between the given table and what I imagine is an ID->Name mapping table:

$links = array(
    array('A' => 1, 'AName' => 'TestPC1', 'B' => 2, 'BName' => 'TestHub2'),
    array('A' => 2, 'AName' => 'TestHub2', 'B' => 3, 'BName' => 'Rack3'),
    array('A' => 2, 'AName' => 'TestHub2', 'B' => 4, 'BName' => 'Rack4')
);

$elements = array();

// Build the tree
foreach ($links as $link) {
    if (!isset($elements[$link['A']])) {
        $elements[$link['A']] = array(
            'id' => $link['A'], 'name' => $link['AName'], 
            'data' => array(), 'children' => array()
        );
    }
    if (!isset($elements[$link['B']])) {
        $elements[$link['B']] = array(
            'id' => $link['B'], 'name' => $link['BName'], 
            'data' => array(), 'children' => array()
        );
    } 
    $elements[$link['A']]['children'][$link['B']] = &$elements[$link['B']]; 
}

// Patch up the indices to start from 0
foreach ($elements as &$element) {
    $element['children'] = array_values($element['children']);
}

$elements = array_values($elements);

Personally I wouldn't bother patching up the indices, it will make it easier searching for entries by ID later if you don't.

Be careful when entering your parent/child data or you'll wind up with a lovely circular reference.

You will of course need to replace the references to $links with a suitable db command that produces an array (mysql_fetch_array etc).