Multidimensional array is overwriting instead of appending

Question

I have a Wordpress site with Woocommerce Subscriptions. I want to list all products and within each product I want to list each subscription (if any) and the subscription status (active, cancelled, on-hold etc). I have managed to list all products in an array, but I have trouble organizing it. Here is an example snippet:

[
    {
        "bod_id": "3557",
        "bod_navn": "CS 01",
        "order_id": "4153",
        "sub_id": "4154",
        "sub_status": "wc-active"
    },
    {
        "bod_id": "3557",
        "bod_navn": "CS 01",
        "order_id": "4570",
        "sub_id": null,
        "sub_status": null
    },
    {
        "bod_id": "3557",
        "bod_navn": "CS 01",
        "order_id": "4631",
        "sub_id": null,
        "sub_status": null
    }
]

Full result page can be found here: https://inboxlager.no/bodoversikt/index4.php

"bod_id" is the product ID and I want to have 1 product per array item. After that comes "bod_navn" which is the name of the product. In the example "order_id" is listed, but it's not needed for anything other than a link between the product and each subscription within the product.

Using the above example I want the result to be more or less like this (subs appended): (The results with null is not really wanted also, I just added them to show appending and to use same example as before)

[
    {
        "bod_id": "3557",
        "bod_navn": "CS 01",
        "subs": {
            "sub_id": "4154" {
                "sub_status": "wc-active"
            },
            "sub_id": "null" {
                sub_status": "null"
            },
            "sub_id": "null" {
                sub_status": "null"
            }
    }
]

After trying a bit to solve this I got a little closer, but still not good enough. A snippet of the result:

{
    "3557": {
        "bod_id": "3557",
        "bod_navn": "CS 01",
        "subs": {
            "sub_id": {
                "sub_status": null
            }
        }
    }
}

Full result page can be found here: https://inboxlager.no/bodoversikt/index5.php

As you see, the [] are gone. But more importantly; the sub_id keeps getting overwritten and only the last row is presented. I have 2 theories how to solve this, but I don't know how to execute them properly.

1. Run a separate array with subs and then somehow merge after.
2. Run an SQL query specific for subs connected to the product ID within the while loop.

Or maybe there is a better way to fix this?

These are the database tables that I can use:

wp_2_posts

ID      post_title      post_status     post_type           post_parent
3557    CS 01           (not used)      product             (not used)
4154    (not used)      wc-active       shop_subscription   4153
4153    (not used)      (not used)      shop_order          (not used)

wp_2_wc_order_product_lookup

order_id    product_id
4153        3557

Here is the PHP file for the first example (this is not organized into per product, but resulting in every combination possible from query):

$boder = array();
$bod_sql = "
SELECT
    A.ID AS bod_id, A.post_title AS bod_navn, B.order_id,
    C.ID AS sub_id, C.post_status AS sub_status
FROM wp_2_posts A
    LEFT JOIN wp_2_wc_order_product_lookup B ON A.ID = B.product_id
    LEFT JOIN wp_2_posts C ON C.post_parent = B.order_id
WHERE A.post_type = 'product'
ORDER BY A.ID";
$bod_res = mysqli_query($conn,$bod_sql) or die("Last error: {$conn->error}\n");

while($bod_row = $bod_res->fetch_assoc()) {
    $boder[] = $bod_row;
}

$json = json_encode($boder, JSON_PRETTY_PRINT);
echo $json;

Here is the PHP file for my attempt (this is where it overwrites subs):

$boder = array();
$bod_sql = "
SELECT
    A.ID AS bod_id, A.post_title AS bod_navn, B.order_id,
    C.ID AS sub_id, C.post_status AS sub_status
FROM wp_2_posts A
    LEFT JOIN wp_2_wc_order_product_lookup B ON A.ID = B.product_id
    LEFT JOIN wp_2_posts C ON C.post_parent = B.order_id
WHERE A.post_type = 'product'
ORDER BY A.ID";
$bod_res = mysqli_query($conn,$bod_sql) or die("Last error: {$conn->error}\n");

while($bod_row = $bod_res->fetch_assoc()) {
    $boder[$bod_row['bod_id']] ['bod_id'] = $bod_row['bod_id'];
    $boder[$bod_row['bod_id']] ['bod_navn'] = $bod_row['bod_navn'];
    $boder[$bod_row['bod_id']] ['subs'] ['sub_id'] = $bod_row['sub_id'];
    $boder[$bod_row['bod_id']] ['subs'] ['sub_id'] ['sub_status'] = $bod_row['sub_status'];
}

$json = json_encode($boder, JSON_PRETTY_PRINT);
echo $json;

Answer 1

As mentioned earlier, the reason that only one sub_id is displayed on the json object is that the associative array keys are unique in PHP. (on a side note the JSON syntax seems slightly incorrect as well).

To achieve something similar to the result you provided, you could go with the following:

while($bod_row = $bod_res->fetch_assoc()) {
    $boder[$bod_row['bod_id']] ['bod_id'] = $bod_row['bod_id'];
    $boder[$bod_row['bod_id']] ['bod_navn'] = $bod_row['bod_navn'];
    
    // Here we create an empty array for subs if that is not yet done.
    if (!isset($boder[$bod_row['bod_id']]['subs'])) {
        $boder[$bod_row['bod_id']]['subs'] = [];
    }

    // Then we push associative arrays of key value pairs
    // for each of the id and status pairs.
    $boder[$bod_row['bod_id']]['subs'][] = [
        'sub_id' => $bod_row['sub_id'],
        'sub_status' => $bod_row['sub_status']
    ];
}

This would result in the following JSON output:

{
    "3557":{
        "bod_id":"3557",
        "bod_navn":"CS 01",
        "subs":[
            {
                "sub_id":"4145",
                "sub_status":"wc-active"
            },
            {
                "sub_id":null,
                "sub_status":null
            },
            {
                "sub_id":null,
                "sub_status":null
            }
        ]
    }
}

To elaborate a little bit more, PHP converts the associative arrays to primitive json objects. And to hold multiple objects you really need an array that can store a collection of them, else it will override/re-assign a value to a property.