Given the following code snippet:

<?php
require "db_conn.php";

$sql = "SELECT category,product_code,product_name,unit,quantity FROM items INNER JOIN categories ON items.category_id=categories.id";

$res = $conn->query($sql)->fetchAll();

$structured = [];

if ($res) {

     foreach ($res as $key => $row)
     {
        $structured[$row['category']]['items'][] = [
           'product_code' => $row['product_code'],
           'product_name' => $row['product_name'],
           'unit' => $row['unit'],
           'quantity' => $row['quantity'],
        ];

     }

     echo json_encode([$structured]);
}

outputs the following JSON response:

[{
"BABY ITEMS": {
    "items": [{
        "product_code": "151128",
        "product_name": "BOUNCY BABY WIPES 80'S",
        "unit": "CARTON",
        "quantity": "5.00"
    }]
},
"CONFECTIONS\/PASTRIES": {
    "items": [{
        "product_code": "130570",
        "product_name": "NUVITA FAMILY 75G",
        "unit": "CARTON",
        "quantity": "1.00"
    }, {
        "product_code": "115165",
        "product_name": "NUVITA MAGIK LEMON CRM 60*2'S",
        "unit": "CARTON",
        "quantity": "2.00"
    }]
},
"HOUSEHOLD ITEMS": {
    "items": [{
        "product_code": "150278",
        "product_name": "BOUNCY BABY DIAPER 10'S MINI",
        "unit": "CARTON",
        "quantity": "1.00"
    }]
}

}]

How do I modify this part of code:

$structured[$row['category']]['items'][]

to output:

"category": "BABY ITEMS",

instead of:

"BABY ITEMS":

The expected JSON is (Handwritten):

[{
        "category": "BABY ITEMS",
        "items": [{
            "product_code": "151128",
            "product_name": "BOUNCY BABY WIPES 80'S",
            "unit": "CARTON",
            "quantity": "5.00"
        }]
    },
    {
        "category": "CONFECTIONS/PASTRIES",
        "items": [{
                "product_code": "130570",
                "product_name": "NUVITA FAMILY 75G",
                "unit": "CARTON",
                "quantity": "1.00"
            },
            {
                "product_code": "115165",
                "product_name": "NUVITA MAGIK LEMON CRM 60*2'S",
                "unit": "CARTON",
                "quantity": "3.00"
            },
            {
                "product_code": "115160",
                "product_name": "NUVITA MAGIK S.BERRY CRM 60*2'S",
                "unit": "CARTON",
                "quantity": "2.00"
            }
        ]
    },
    {
        "category": "HOUSEHOLD ITEMS",
        "items": [{
            "product_code": "150278",
            "product_name": "BOUNCY BABY DIAPER 10'S MINI",
            "unit": "CARTON",
            "quantity": "1.00"
        }]
    },
    {
        "category": "PERSONAL CARE",
        "items": [{
            "product_code": "160200",
            "product_name": "ALL TYME ULTRA REGULAR 8'S",
            "unit": "CARTON",
            "quantity": "2.00"
        },
        {
            "product_code": "160309",
            "product_name": "ALL TYME ULTRA MEDIUM 8'S",
            "unit": "CARTON",
            "quantity": "4.00"
        },
        {
            "product_code": "160235",
            "product_name": "GOLDEN SHOE POLSIH 50ML BLACK",
            "unit": "CARTON",
            "quantity": "1.00"
        },
        {
            "product_code": "190251",
            "product_name": "ALL TYME ULTRA MEDIUM 16'S",
            "unit": "CARTON",
            "quantity": "2.00"
        }]
    }
]

I really need the JSON formatted as above as am using it to create a nested category and items RecyclerView. This is how am parsing in android java:

 for (int i = 0; i < jsonArray.length(); i  ) {
   JSONObject objCategory = jsonArray.getJSONObject(i);

   System.out.println("Category: "   objCategory.getString("category"));

   // fetch item details and store it in arraylists
   JSONArray itemArray = objCategory.getJSONArray("items");
   //System.out.println("Item length: "   itemArray.length());

   ParentItem category = new 
   ParentItem(objCategory.getString("category"),OrderItemsList(itemArray));
   orderFetchingList.add(category);

}

CodePudding user response：

Please see the following code (fully functional). I mocked $res with few records and copied your code to fill $structured. Below that, please find my code to restructure the data in order the get the desired JSON output

$res = [
    [
        'category' => 'BABY',
        'product_code' => '123',
        'product_name' => 'BABY STUFF',
        'unit' => 1,
        'quantity' => 999,
    ],
    [
        'category' => 'BABY',
        'product_code' => '125',
        'product_name' => 'BABY EAT',
        'unit' => 1,
        'quantity' => 999,
    ],
    [
        'category' => 'MEN',
        'product_code' => '124',
        'product_name' => 'MEN STUFF',
        'unit' => 2,
        'quantity' => 888,
    ]
];


echo '<pre>';
$structured = [];

foreach($res as $key => $row) {
    $structured[$row['category']]['items'][] = [
       'product_code' => $row['product_code'],
       'product_name' => $row['product_name'],
       'unit' => $row['unit'],
       'quantity' => $row['quantity'],
    ];
}

print_r($structured);

// restructure the data to get the desired JSON output
$collection = [];
foreach($structured as $category => $items) {
    $collection[] = [
        'category' => $category,
        'items' => $items['items']
    ];
}

echo json_encode($collection);

Output (JSON):

[
    {
        "category": "BABY",
        "items": [
            {
                "product_code": "123",
                "product_name": "BABY STUFF",
                "unit": 1,
                "quantity": 999
            },
            {
                "product_code": "125",
                "product_name": "BABY EAT",
                "unit": 1,
                "quantity": 999
            }
        ]
    },
    {
        "category": "MEN",
        "items": [
            {
                "product_code": "124",
                "product_name": "MEN STUFF",
                "unit": 2,
                "quantity": 888
            }
        ]
    }
]