Group nested array from SQL query with PHP to get JSON for jqTree plugin

Question

this is my first post on SO, so I apologize if this is the wrong area for this question.

I have a page written in PHP, and I'm trying to create a tree with a specific layout using a jquery plugin called jqTree. I'm pulling my data on page load from a SQL Server 2008 R2 database with the following return format:

╔═════════════╦═══════╦═════════╗
║  Location   ║  SAN  ║  Switch ║
╠═════════════╬═══════╬═════════╣
║ LocationA   ║ SAN1  ║ Switch1 ║
║ LocationA   ║ SAN1  ║ Switch2 ║
║ LocationA   ║ SAN2  ║ Switch3 ║
║ LocationB   ║ SAN3  ║ Switch4 ║
║ LocationB   ║ SAN3  ║ Switch5 ║
║ LocationB   ║ SAN3  ║ Switch6 ║
╚═════════════╩═══════╩═════════╝

I need the tree to look like:

LocationA
    |_ SAN1
    |    |_ Switch1
    |    |_ Switch2
    |_ SAN2
        |_ Switch3

LocationB
    |_ SAN3
         |_ Switch4
         |_ Switch5
         |_ Switch6

According to the jqTree site, the JSON must be in the following format (I'm assuming):

var data = [
{
    label: 'LocationA',
    children: [
        { label: 'SAN1',
          children: [
               { label: 'Switch1'},
               { label: 'Switch2'}
              ]
        },
        { label: 'SAN2',
          children: [
               { label: 'Switch3'}
              ]
        }
},
{
    label: 'LocationB',
    children: [
        { label: 'SAN1',
          children: [
               { label: 'Switch4'},
               { label: 'Switch5'},
               { label: 'Switch6'}
              ]
}
}
];

I've tried things to get close like:

$locations = array();

foreach ($results as $res) {
if(!in_array($res['town'], $locations, true)){

    $locations[$res['town']]['children'][$res['san']][] = $res['name'];
}
}

But it's clearly not correct...

I've looked at nesting array solutions and something is just not clicking for me, so I was hoping someone could help me out with this. I'm fine with using another plugin, as long as i can have the same/similar functionality.

Hopefully I was able to portray the situation well enough, but let me know if you need more data.

Thanks in advance!

Answer 1

Here is the array that creates your example JSON:

$data = array(
    array(
        'label' => 'LocationA',
        'children' => array(
            array(
                'label' => 'SAN1',
                'children' => array(
                    array('label' => 'Switch1'),
                    array('label' => 'Switch2')
                )
            ),
            array(
                'label' => 'SANS',
                'children' => array(
                    array('label' => 'Switch3')
                )
            )
        )
    ),
    array(
        'label' => 'LocationB',
        'children' => array(
            array(
                'label' => 'SAN1',
                'children' => array(
                    array('label' => 'Switch4'),
                    array('label' => 'Switch5'),
                    array('label' => 'Switch6')
                )
            )
        )
    )
);

echo json_encode($data);

I believe this should get your data into that format:

$locations = array();

foreach ($results as $res) {
    $locations[ $res['town'] ][ $res['san'] ][] = $res['name'];
}

$data = array();
foreach ($locations as $location => $sans) {
    $location_array = array('label' => $location);
    foreach ($sans as $san => $names) {
        $san_array = array('label' => $san);
        foreach ($names as $name) {
            $san_array['children'][] = array('label' => $name);
        }
        $location_array['children'][] = $san_array;
    };
    $data[] = $location_array;
}

echo json_encode($data);