How to get data from database as nested JSON array?

Question

I'm trying to pull data from MySQL using PHP/PDO and format it as a nested JSON array.

Here is what I am getting:

{
    "host1": [
        {
            "vmnic_name": "vmnic0",
            "switch_name": "switch1",
            "port_id": "GigabitEthernet1\/0\/1"
        },
        {
            "vmnic_name": "vmnic1",
            "switch_name": "switch1",
            "port_id": "GigabitEthernet1\/0\/2"
        }
    ],
    "host2": {
        "2": {
            "vmnic_name": "vmnic0",
            "switch_name": "switch1",
            "port_id": "GigabitEthernet1\/0\/3"
        },
        "3": {
            "vmnic_name": "vmnic1",
            "switch_name": "switch1",
            "port_id": "GigabitEthernet1\/0\/4"
        }
    }
}

I'd like for it to say "host_name": "host1", etc., rather than just "host1". And for hosts after the first to not have numbers like "2" or "3" like how the first host is.

Here is my code:

$arr = array();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $key => $item) {
    $arr[$item['host_name']][$key] = array(
            'vmnic_name'=>$item['vmnic_name'],
            'switch_name'=>$item['switch_name'],
            'port_id'=>$item['port_id']
    );
}
echo json_encode($arr, JSON_PRETTY_PRINT);

I'd like for it to say "host_name": "host1": could you please clarify what the expected output would need to be? It is not clear how the structure would be. — trincot
– trincot, Commented Dec 20, 2019 at 15:57

AbraCadaver · Accepted Answer · 2019-12-20 16:21:09Z

1

If you only select those columns in the query, then it's as simple as this:

foreach ($result as $item) {
    $arr[$item['host_name']][] = $item;
}

If for whatever reason you must select more columns for later use, then just insert host_name and remove the $key as the index:

foreach ($result as $item) {
    $arr[$item['host_name']][] = array(
            'host_name'=>$item['host_name'],
            'vmnic_name'=>$item['vmnic_name'],
            'switch_name'=>$item['switch_name'],
            'port_id'=>$item['port_id']
    );
}

edited Dec 20, 2019 at 16:21

answered Dec 20, 2019 at 15:59

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

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

Comments

delboy1978uk · Accepted Answer · 2019-12-20 15:59:05Z

This is pretty easy. Decode it to an array, then stick it in another one.

$array = json_decode($json, true);
$x = [];
$x['host_name'] = $array;

var_dump(json_encode($x, JSON_PRETTY_PRINT));

Which will give you:

string(747) "{ "host_name": { "host1": [ { "vmnic_name": "vmnic0", "switch_name": "switch1", "port_id": "GigabitEthernet1\/0\/1" }, { "vmnic_name": "vmnic1", "switch_name": "switch1", "port_id": "GigabitEthernet1\/0\/2" } ], "host2": { "2": { "vmnic_name": "vmnic0", "switch_name": "switch1", "port_id": "GigabitEthernet1\/0\/3" }, "3": { "vmnic_name": "vmnic1", "switch_name": "switch1", "port_id": "GigabitEthernet1\/0\/4" } } } }"

https://3v4l.org/PlbON

Collectives™ on Stack Overflow

How to get data from database as nested JSON array?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related