How to fetch the data for multiple values Categorywise using mysqli and PHP

Question

I am creating a simple database. I have a categories table which looks like this:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(256) NOT NULL,
   PRIMARY KEY (`id`)
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

And a products table which looks like this:

CREATE TABLE IF NOT EXISTS `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `recipe_name` varchar(32) NOT NULL,
 `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Now I want to Fetch the data like this :

{
"data": {
    "menu": [
        {
            "id": "4821076d99ca1ad5fdb3aba31c19a8a7730533a6f7ea5b0a",
            "category_name": "Italian",
            "recipes": [
                {
                    "id": "7728135",
                    "recipe_name": "Pizza",  
                },
                {
                    "id": "7728136",
                    "recipe_name": "Garlik bread",
                },
                {
                    "id": "7951066",
                    "recipe_name": "Pasta",
                }
            ]
        },
    ]
},
"status": "1",
"message": ""

}

I am using below query but it doesn't work...

$query = "SELECT c.category_name as category_name, p.id, 
          p.recipe_name,p.category_id FROM
            " . $this->table_name . " p
            LEFT JOIN categories c
                    ON p.category_id = c.id";

Using Above query I was get my response like this:

{
  "data":[
           {
             "category_id":"5",
             "category_name":"Healthy Eating",
             "recipes":[
                        {
                         "id":"18",
                         "recipe_name":"GARDEN SALAD"
                        }
                      ]
           },
           {
            "category_id":"5",
            "category_name":"Healthy Eating",
            "recipes":[
                       {
                        "id":"19",
                        "recipe_name":"Vegetable salad"
                       }
                      ]
           }
       ]
}

Answer 1

Here's some sample code. You need to adjust mysql connect details.

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$tableName = 'recipe';
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$query = "SELECT c.category_name as category_name, p.id, 
          p.recipe_name,p.category_id FROM
            " . $tableName . " p
            LEFT JOIN categories c
                    ON p.category_id = c.id";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

    $_category = null;
    $_array_result = [];
    $_i = 0;
    // output data of each row
    while ($row = $result->fetch_assoc()) {
        if ($row['category_id'] != $_category) {
            $_category = $row['category_id'];
            $_array_result[$_i]['category_name'] = $row['category_name'];
            $_array_result[$_i]['category_id'] = $row['category_id'];
            $_i++;
        }
        $_array_result[$_i]['recipes'][] = ['id' => $row['id'], 'name' => $row['name']];
    }

    $final_array = ['menu' => $_array_result];

    echo json_encode($final_array);

    $conn->close();