[FIXED] How to set sub json array in one array PHP

Issue

I am new in php, I was trying to fetch data that I get from the following response but I want to set this data in sub array. How to do this? I have two different tables category and Product. How to Show Multiple Products Category wise.

Thanks in Advance!

{
    "data" : [
        {
            "id" : "1",
            "recipe_name" : "Tofu Tikka",
            "ingredients" : "Firm tofu 1 pack (bite sized cube)\r\n",
            "prepration" : "Press tofu with the help of plate to remove moisture 
   and leave for 30-40 minutes, then cut in cubes.\r\n",
            "category_id":"1",
            "category_name":"Today's Menu"
        }
    ]
}

How to set above Response in sub Array like following way

{
    "data":[
        "category_id":"1",
        "category_name":"Today's Menu"
        "recipes::[
            {
                "id":"1",
                "recipe_name":"Tofu Tikka",
                "ingredients":"Firm tofu 1 pack ",
                "prepration":"Press tofu with the help of plate"
            }, {
                "id":"2",
                "recipe_name":"Tikka Paneer",
                "ingredients":"Firm tofu 1 pack ",
                "prepration":"Press tofu with the help of plate"
            },
        ]
    ]
}

Below is my PHP File

<?php
    // required headers
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");

    // include database and object files
    include_once '../config/database.php';
    include_once '../objects/product.php';

    // instantiate database and product object
    $database = new Database();
    $db = $database->getConnection();

    // initialize object
    $product = new Product($db);

    // query products
    $stmt = $product->read();
    $num = $stmt->rowCount();

    // check if more than 0 record found
    if ($num>0) {

        // products array
        $products_arr=array();
        $products_arr["data"]=array();

        // retrieve our table contents
        // fetch() is faster than fetchAll()
        // http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            // extract row
            // this will make $row['name'] to
            // just $name only
            extract($row);

            $product_item=array(
                "id" => $id,
                "recipe_name" => $recipe_name,
                "ingredients" => html_entity_decode($ingredients),
                "prepration" => $prepration,
                "category_id" => $category_id,
                "category_name" => $category_name
            );

            array_push($products_arr["data"], $product_item);
        }

        echo json_encode($products_arr);

    } else {
        echo json_encode(
        array("message" => "No products found.")
        );
    }
?>

Solution

I will suggest you to use JOIN while getting records of category and its related products. It will need single query and single loop to generate array you want. Here is the sample query which you can use. It will get category name with each product record and do not show those categories who do not have products in it.

SELECT * FROM categories AS c LEFT JOIN offers AS p ON c.category_id=p.category_id WHERE p.offer_id IS NOT NULL

Note: - Do not use asterisk (*) in your search query, use table field names instead.

<?php

// initialize empty category array
$categoryArr = [];
// $row has product info with category id and name in it.
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){

    /* First key of categoryArr variable is category id. It will automatically create array key for each category.
    *   If an array key already exists, it will add prodcts in it.
    */
    $categoryArr[$row['category_id']]['category_id'] = $row['category_id'];
    $categoryArr[$row['category_id']]['category_name'] = $row['category_name'];
    $categoryArr[$row['category_id']]['products'][] = $row;
}
/* Once loop done with its work. Need to reset array keys with the help of below function. */
$result = array_values($categoryArr);

echo json_encode($result); ?>

I have not tested it, its just to give you an idea. I hope you will improve it.

Answered By - Mohit Saini