CODEWITHSUNDEEP
phpmysqlsqlarraysjson
Lyndon Penson
Lyndon Penson

Reputation: 65

mysql result to multidimensional json array in different structure

I am struggling to achieve the correct json array format from the mysqli resultset. I have googled extensively and tried different things.

I am sql querying e-commerce orders and attempting to output them in JSON format to post to an application, in the JSON format specified by the application developer.

First tried this, outputs each line separately , not what I want:

while ( $row = $result->fetch_assoc())  {
    $orders[]=$row; 
} 
echo json_encode($orders, JSON_PRETTY_PRINT);

The result was 

[
    {
        "WebOrderNumber_C": "938276",
        "itemName": "B3440S"
    },
    {
        "WebOrderNumber_C": "938276",
        "itemName": "D5035G"
    },
    {
        "WebOrderNumber_C": "938276",
        "itemName": "D6015"
    }
]

Second having googled again and read other questions on here, I tried this

while ( $row = $result->fetch_assoc())  {
$orders[$row['WebOrderNumber_C']][] = $row['itemName'];
} 
echo json_encode($orders, JSON_PRETTY_PRINT);

The result was

{
    "938276": [
        "B3440S",
        "D5035G",
        "D6015"
    ]
}

The format I am trying to achieve is this. please help

{
    "WebOrderNumber_C": "938276",
    "shipAddress": {
        "add1": "LONDON"
    },
    "items": [{
            "itemName": "B3440S"
        },
        {
            "itemName": "B3440S"
        },
        {
            "itemName": "B3440S"
        }
    ]
}

PS I am Using PHP 5.6.30 if that is relevant.

Upvotes: 1

Views: 84

Answers (1)

Barmar
Barmar

Reputation: 780724

Since the array you're adding to is nested, you need to create the parent object the first time you encounter a row with that order number. You can use an associative array for that, to make it easy to tell if the object already exists.

Then you add to the nested array, and wrap the item name in the associative array with the itemName key.

while ( $row = $result->fetch_assoc())  {
    $orderno = $row['WebOrderNumber_C'];
    if (!isset($orders[$orderno])) {
        $orders[$orderno] = [
            "WebOrderNumber_C" => $orderno,
            "shipAddress" => [
                "add1" => $row["add1"],
                // other fields here ...
            ],
            "items" => []
        ];
    }
    $orders[$orderno]["items"][] = ["itemName" => $row['itemName']];
}
$orders = array_values($orders); // Convert from associative array to indexed
echo json_encode($orders, JSON_PRETTY_PRINT);

Upvotes: 3

Related Questions