使用 PHP MySQL 创建嵌套 JSON

我有一个返回某些字段的 SQL 查询，我正在使用json_encode()获取 JSON 格式的数据，但是我无法以我想要的格式获取它。

PHP代码

<?php

function data() {
    $runDistanceBasedOnCityQuery = "SELECT rc.id, rc.cityId, c.cityName, rc.runId, r.distance, rc.status FROM run_city rc INNER JOIN cities c ON c.id = rc.cityId INNER JOIN run_distance r ON r.id = rc.runId ORDER BY c.cityName";
    $runDistanceBasedOnCityResult = $db->prepare($runDistanceBasedOnCityQuery);
    $runDistanceBasedOnCityResult->bindParam(":cityId", $cityId, PDO::PARAM_INT);
    $runDistanceBasedOnCityResult->execute();
    $runDistanceBasedOnCityOutput = $runDistanceBasedOnCityResult->rowCount();
    if ($runDistanceBasedOnCityOutput > 0) {
        while ($runDistanceBasedOnCityRow = $runDistanceBasedOnCityResult->fetch(PDO::FETCH_ASSOC)) {
            $array1 = array($runDistanceBasedOnCityRow['runId'], $runDistanceBasedOnCityRow['distance'], $runDistanceBasedOnCityRow['status']);
            for ($i = 0; $i < sizeof($array1); $i++) {
                $array2 = array("id" => $runDistanceBasedOnCityRow['id'], "runId" => $runDistanceBasedOnCityRow['cityId'], $runDistanceBasedOnCityRow['cityName'] => $array1);
            }

            $finalResultRunDistanceBasedOnCity[] = $array2;
        }
        $responseRunDistanceBasedOnCity = $finalResultRunDistanceBasedOnCity;
    } else {
        $responseRunDistanceBasedOnCity = 'Runs not found';
    }

    $result = array("status" => true,
        "runsBasedOnCity" => $responseRunDistanceBasedOnCity
    );

    json($result);
}

function json($data) {
    header('Content-Type:application/json');
    if (is_array($data)) {
        echo json_encode($data);
    }
}
?>

我得到的 JSON 格式

"runsBasedOnCity": [
    {
        "id": "1",
        "runId": "1",
        "Bengaluru": [
            "2",
            "10k",
            "1"
        ]
    },
    {
        "id": "2",
        "runId": "1",
        "Bengaluru": [
            "1",
            "5k",
            "1"
        ]
    },
    {
        "id": "3",
        "runId": "1",
        "Bengaluru": [
            "5",
            "3k",
            "0"
        ]
    },
    {
        "id": "4",
        "runId": "2",
        "Chennai": [
            "1",
            "5k",
            "1"
        ]
    },
    {
        "id": "5",
        "runId": "2",
        "Chennai": [
            "2",
            "10k",
            "1"
        ]
    },
    {
        "id": "6",
        "runId": "2",
        "Chennai": [
            "4",
            "15k",
            "1"
        ]
    }
]

我需要的格式

"runsBasedOnCity": [
    {
        "id": "1",
        "cityId": "1",
        "Bengaluru": 
         [
            {
              runId : "2",
              distance : "10k",
              status : "1"
            },
            {
              runId : "1",
              distance: "5k",
              status : "1"
            },
            {
              runId : "5",
              distance : "3k",
              status : "0"
            }
        ]
     },
     {
        "id": "2",
        "cityId": "2",
        "Chennai": 
         [
            {
              runId : "1",
              distance : "5k",
              status : "1"
            },
            {
              runId : "2",
              distance: "10k",
              status : "1"
            },
            {
              runId : "4",
              distance : "15k",
              status : "1"
            }
        ]
     }

我无法找出更好的方法来做到这一点，我对此相当陌生，请帮助我。谢谢 ！

为了有效地对子数组数据进行分组，您应该实现临时键。cityId是一个合适的分组依据值——因为cityNames将来可能会故意重复，但是cityId决不能在数据库表中无意/无意地重复。

当每一个新cityId遇到时，有条件的isset()调用将确定是否应存储新的/完整的数据集，或者是否应仅将数据附加到子数组。

我正在打电话array_slice()因为它减少了不必要的语法/代码膨胀。

遍历所有行后，您可以重新索引$result数组，将其嵌套在里面runBasedOnCity，并添加status元素。

我将展示我的演示PRETTY_PRINT这样更容易阅读，但在实际代码中，您应该删除该参数。另外，有一条建议——尽量保持变量名称简短，以提高可读性。

Code: (Demo https://3v4l.org/f6Ecq)

$resultset = [
    ["id" => "1", "cityId" => "1", "cityName" => "Bengaluru", "runId" => "2", "distance" => "10k", "status" => "1"],
    ["id" => "2", "cityId" => "1", "cityName" => "Bengaluru", "runId" => "1", "distance" => "5k", "status" => "1"],
    ["id" => "3", "cityId" => "1", "cityName" => "Bengaluru", "runId" => "5", "distance" => "3k", "status" => "0"],
    ["id" => "4", "cityId" => "2", "cityName" => "Chennai", "runId" => "1", "distance" => "5k", "status" => "1"],
    ["id" => "5", "cityId" => "2", "cityName" => "Chennai", "runId" => "2", "distance" => "10k", "status" => "1"],
    ["id" => "6", "cityId" => "2", "cityName" => "Chennai", "runId" => "4", "distance" => "15k", "status" => "1"]
];

foreach ($resultset as $row) {
    if (!isset($result[$row["cityId"]])) {
        $result[$row["cityId"]] = array("id" => $row["id"], "cityId" => $row["cityId"], $row["cityName"] => array(array_slice($row,-3)));
    } else {
        $result[$row['cityId']][$row["cityName"]][] = array_slice($row,-3);
    }
}

if (!isset($result)) {   // don't need to check rowCount() at all
    $result = 'Runs not found';
} else {
    $result = array_values($result);
}

$result = array("status" => true, "runsBasedOnCity" => $result);

var_export(json_encode($result, JSON_PRETTY_PRINT));

Output:

'{
    "status": true,
    "runsBasedOnCity": [
        {
            "id": "1",
            "cityId": "1",
            "Bengaluru": [
                {
                    "runId": "2",
                    "distance": "10k",
                    "status": "1"
                },
                {
                    "runId": "1",
                    "distance": "5k",
                    "status": "1"
                },
                {
                    "runId": "5",
                    "distance": "3k",
                    "status": "0"
                }
            ]
        },
        {
            "id": "4",
            "cityId": "2",
            "Chennai": [
                {
                    "runId": "1",
                    "distance": "5k",
                    "status": "1"
                },
                {
                    "runId": "2",
                    "distance": "10k",
                    "status": "1"
                },
                {
                    "runId": "4",
                    "distance": "15k",
                    "status": "1"
                }
            ]
        }
    ]
}'

在解释了您想要如何保存之后id子数组中的值，这是解决方案：

Code: (Demo https://3v4l.org/5fRfJ)

foreach ($resultset as $row) {
    if (!isset($result[$row["cityId"]])) {
        $result[$row["cityId"]] = array("cityId" => $row["cityId"], $row["cityName"] => array(array("id" => $row["id"])+array_slice($row,-3)));
    } else {
        $result[$row['cityId']][$row["cityName"]][] = array("id" => $row["id"])+array_slice($row,-3);
    }
}

if (!isset($result)) {   // don't need to check rowCount() at all
    $result = 'Runs not found';
} else {
    $result = array_values($result);
}

$result = array("status" => true, "runsBasedOnCity" => $result);
var_export(json_encode($result, JSON_PRETTY_PRINT));