如何使用mysqli和php获取多值类别的数据

vnzz0bqm 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(321)

我正在创建一个简单的数据库。我有一个如下所示的分类表：

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;

产品表如下所示：

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;

现在我想获取如下数据：

{
"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": ""

}
我使用下面的查询，但它不工作。。。

$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";

使用上述查询，我得到的答复如下：

{
  "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"
                       }
                      ]
           }
       ]
}

sql mysql php sql-query-store

来源：https://stackoverflow.com/questions/48059564/how-to-fetch-the-data-for-multiple-values-categorywise-using-mysqli-and-php

1条答案

按热度按时间

wgxvkvu91#

下面是一些示例代码。您需要调整mysql connect的详细信息。

$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();

赞(0）回复(0）举报 2021-06-21

我来回答

如何使用mysqli和php获取多值类别的数据

1条答案

相关问题

热门标签

最新问答