php - 如何在 php 中从 mysql 数据库创建 JSON API 服务

Question

我有以下 mysql 数据库表

id | majorFoodName | cropName | foodType  | foodName  | quantity | form 
1  | Recipe1       | Rice     | Breakfast | Foodname1 | 500+60   | 2000 
4  | Recipe2       | Rice     | Breakfast | Foodname2 | 500      | 1000
6  | Recipe1       | Wheat    | Breakfast | Foodname2 | 518      | 1000 

我编写了以下 php 代码来提供 JSON API 输出

$sql = "SELECT * FROM food WHERE cropName = 'Rice' AND foodName = 
'Foodname1' ";
$result = mysqli_query($connect, $sql);
$num_rows = mysqli_num_rows($result);
if ($num_rows > 0) {
    $jsonData = array();
    while ($array = mysqli_fetch_row($result)) {
    $jsonData[] = $array;
  }
}
class Emp {
  public $majorFoods = "";    
   }
   $e = new Emp();
   $e->majorFoods = $jsonData;
   header('Content-type: application/json');
   echo json_encode($e);

我得到以下 JSON 输出

{
    "majorFoods": [
        [
        "1",
        "Recipe1",
        "Rice",
        "Breakfast",
        "Foodname1",
        "500+60",
        "2000"
    ]
  ]
}

我需要为所有cropName和所有foodName提供以下API JSON格式

{
  "Rice": [
               {
                 "foodName1": {
                        "majorFoodName": "Receipe1",
                        "quantity": "500+60",
                        "form": "2000" }
                 "foodName2": {
                        "majorFoodName": "Receipe2",
                        "quantity": "500",
                        "form": "1000" } 
                 ]
  "Wheat": [
               {
                 "foodName2": {
                        "majorFoodName": "Receipe1",
                        "quantity": "518",
                        "form": "1000" }
                 ]
}

请帮助改进 php 代码以获得所需的 API JSON 响应。

Answer 1

构建数据数组时，使用裁剪类型作为主索引，并创建需要存储的额外数据的子数组。

 while ($array = mysqli_fetch_assoc($result)) {
    $cropData = array ( $array['foodName'] => 
                array( 'majorFoodName' => $array['majorFoodName'],
                       'quantity' => $array['quantity'],
                       'form' => $array['form'] ));
    $jsonData[$array['cropName']][] = $cropData;
 }

请注意，我使用mysqli_fetch_assoc，以便我可以使用列名称引用结果字段。

线路

$jsonData[$array['cropName']][] = $cropData;

将特定裁剪名称的所有数据累积在一起，将新数据添加到数组末尾(使用 [])。