creating nested JSON object in PHP

Question

I'm trying to create a JSON object to submit to an API.

Here is an example of what I'm trying to achieve:

{
    packages: [
        {
            orderNumber: 10000004,
            fulfillmentPackageId: "ShipCo #123456",
            warehouseId: 4,
            carrier: "USPS",
            tracking: "12345667890002394230",
            service_level: "First Class",
            products: [
                {sku: "testSku1", quantity: 5}
            ]
        },
        {
            orderNumber: 10000004,
            fulfillmentPackageId: "ShipCo #123457",
            warehouseId: 4,
            carrier: "USPS",
            tracking: "12345667890002394231",
            service_level: "Priority",
            products: [
                {sku: "testSku2", quantity: 1},
                {sku: "testSku3", quantity: 4}
            ]
        }
    ]
}

I can create the main part of it no problem but I'm getting confused about the 'products' part. It seems to be another json object within the first one. How do I go about adding this?

I currently have this to create the main body of the object, its reading data from a MYSQL table and this is in a loop going through the table results.

        $package = new stdClass();

        $package->orderNumbner = $row['zentail_order_no'];
        $package->fulfillmentPackageId = $row['fulfillment_id'];
        $package->warehouseId = 2;
        $package->carrier = $row['carrier'];
        $package->tracking = $row['tracking'];
        $package->service_level = $row['service_level'];
        $package->products = getPackages($row['zentail_order_no']);

As you can see I'm then trying to populate the products part of the object via another function getPackages().

function getPackages($zentail_order_no){
   
    include 'sql/config.php';
    include 'sql/opendb.php';
   
    $sql = "SELECT * FROM zentail WHERE zentail_order_no = '{$zentail_order_no}'";
    $result = $db->query($sql);
   
    if ($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) {

            $products->sku = $row['sku'];
            $products->quantity = $row['qty'];
   
      }
    }

    return $products;
}

There are multiple lines in the DB with the same order number but different values for SKU and QTY, I was hoping as it cycled over the rows it would add these to the object like it would with arraypush() and then add this into the products field of the original object.

Im sure its something relatively simple, Im finding it hard to get the correct terminology for what Im trying to acheive so its making searching for an answer quite tricky.

Thanks

Answer 1

You need to do minimal change in your getPackages function:

include 'sql/config.php';
include 'sql/opendb.php';

function getPackages($zentail_order_no){
    global $db;

    $products = [];
    
    $sql = "SELECT * FROM zentail WHERE zentail_order_no = '{$zentail_order_no}'";
    $result = $db->query($sql);
   
    if ($result->num_rows > 0) {
      while($row = $result->fetch_assoc()) {
            array_push($products, [
                'sku'=>$row['sku'],
                'qty'=>$row['qty']
            ]);  
      }
    }

    return $products;
}

Here live example: PHPize.online

Answer 2

Make $products an array in your function.

But first start to code using bound, prepared parameterised queries to protect from SQL Injection Attack.

function getPackages($zentail_order_no){
   
    include 'sql/config.php';
    include 'sql/opendb.php';
   
    
    // select only what you need, makes the query quicker, 
    // and allows you fetch the object without more fiddling
    $sql = "SELECT sku, qty FROM zentail 
            WHERE zentail_order_no = ?";
    $stmt = $db->prepare($sql);
    $stmt->bind_param('i', $zentail_order_no);

    $db->execute($sql);
    $result = $stmt->get_result();
   
    $products = [];
    
    // now as you want an object, you can fetch the row as an object
    // rather than an array and then fiddle it into an object
    while($row = $result->fetch_object()) {
        $products[] = $row;
    }
    return $products;
}

Answer 3

You can use arrays to create JSON easily. From your example, here's the solution:

<?php

$package = [
    "orderNumbner" => $row['zentail_order_no'],
    "fulfillmentPackageId" => $row['fulfillment_id'],
    "warehouseId" => 2,
    "carrier" => $row['carrier'],
    "tracking" => $row['tracking'],
    "service_level" => $row['service_level'],
    "products" => getPackages($row['zentail_order_no']),
];

function getPackages($zentail_order_no) {
    include 'sql/config.php';
    include 'sql/opendb.php';

    // Warning, potential SQL injection
    $sql = "SELECT * FROM zentail WHERE zentail_order_no = '{$zentail_order_no}'";
    $result = $db->query($sql);

    $products = [];
    if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $products[] = [
                "sku" => $row['sku'],
                "quantity" => $row['qty'],
            ];
        }
    }

    return $products;
}

echo json_encode($package);