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

If you put it all into a nested array and then use json_encode() on that array you should be fine. — Jay Blanchard
– Jay Blanchard, Commented Sep 2, 2020 at 15:16

IncredibleHat · Accepted Answer · 2020-09-02 15:34:14Z

3

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;
}

edited Sep 2, 2020 at 15:34

IncredibleHat

4,1024 gold badges18 silver badges29 bronze badges

answered Sep 2, 2020 at 15:17

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

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

3 Comments

RiggsFolly Over a year ago

HAhha, thanks @IncredibleHat or should that be $thanks

RiggsFolly Over a year ago

No arguement, that was a real Thanks @IncredibleHat, specially as it didnt come with a DV as is the norm

IncredibleHat Over a year ago

Naw, all's good, I saw the goof, figured, meh, I'll fix it for him ! I hate giving DVs on answers, its really gotta be a terrible answer. Which yours is good! Speaking of which ...

Chtiadrien · Accepted Answer · 2020-09-02 15:29:37Z

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);

answered Sep 2, 2020 at 15:29

Chtiadrien

564 bronze badges

1 Comment

IncredibleHat Over a year ago

Just punctuating the important note about SQL injection. To the OP, be sure to heed all the warnings everyone is giving you on that. It'll save you!

Slava Rozhnev · Accepted Answer · 2020-09-02 15:38:44Z

0

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

answered Sep 2, 2020 at 15:38

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

creating nested JSON object in PHP

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related