PHP customize JSON response

Given PHP code snippet below:

    require "db_conn.php";

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

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

    if ($res) {
       echo json_encode($res);
    }

Am getting the following JSON response:

[{
	"category": "BABY ITEMS",
	"0": "BABY ITEMS",
	"product_code": "151128",
	"1": "151128",
	"product_name": "BOUNCY BABY WIPES 80'S",
	"2": "BOUNCY BABY WIPES 80'S",
	"unit": "CARTON",
	"3": "CARTON",
	"quantity": "5.00",
	"4": "5.00"
}, {
	"category": "CONFECTIONS\/PASTRIES",
	"0": "CONFECTIONS\/PASTRIES",
	"product_code": "130570",
	"1": "130570",
	"product_name": "NUVITA FAMILY 75G",
	"2": "NUVITA FAMILY 75G",
	"unit": "CARTON",
	"3": "CARTON",
	"quantity": "1.00",
	"4": "1.00"
}, {
	"category": "HOUSEHOLD ITEMS",
	"0": "HOUSEHOLD ITEMS",
	"product_code": "150278",
	"1": "150278",
	"product_name": "BOUNCY BABY DIAPER 10'S MINI",
	"2": "BOUNCY BABY DIAPER 10'S MINI",
	"unit": "CARTON",
	"3": "CARTON",
	"quantity": "1.00",
	"4": "1.00"
}]

But I want to achieve the JSON below. Notice the items array inside category object. (NOTE: This is hand generated)

[{
		"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": "2.00"
			}
		]
	},
	{
		"category": "HOUSEHOLD ITEMS",
		"items": [{
			"product_code": "150278",
			"product_name": "BOUNCY BABY DIAPER 10'S MINI",
			"unit": "CARTON",
			"quantity": "1.00"
		}]
	}
]

Any hint will be highly appreciated.

Sergiu17

4 years ago

Best Answer

Level 60

one option

$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'],
    ];
}

echo json_encode($structured);

1 like

kenprogrammer

4 years ago

Level 2

@Sergiu17 This works nice just one more hint. I've edited it like this so that I can I've the key "category" on every category block as am using it to get the category name in android java:

$structured = [];

    if ($res) {

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

       echo json_encode([$structured]);
    }

Now am getting the following response:

[{
	"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"
		}]
	},
	"category:HOUSEHOLD ITEMS": {
		"items": [{
			"product_code": "150278",
			"product_name": "BOUNCY BABY DIAPER 10'S MINIIIIIIII",
			"unit": "CARTON",
			"quantity": "1.00"
		}]
	}
}]

I want the category to look like this:

"category:BABY ITEMS",

Dunsti

4 years ago

Level 6

I think I would use an API-Resource for that:

https://laravel.com/docs/9.x/eloquent-resources

kenprogrammer

4 years ago

Level 2

@Dunsti Am using vanilla PHP since am updating an existing API

Please or to participate in this conversation.