使用PHP MySQL创建嵌套的JSON [英] Creating nested JSON using PHP MySQL
问题描述
我有一个返回某些字段的SQL查询，我正在使用json_encode()以JSON格式获取数据，但是在以所需格式获取数据时遇到了麻烦.
I have an SQL Query returning certain fields, I am using json_encode() to get the data in JSON format, however I am having trouble getting it in the format I want.
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"
}
]
}
我无法找到一种更好的方法来做，对此我还很陌生，请帮帮我.谢谢！
I am not able to figure out a better way of doing this, I am fairly new to this, do help me out. Thanks !
推荐答案
要有效地对子数组数据进行分组，应实现临时键. cityId是按-分组的合适值，因为cityNames将来可能会有意复制，但cityId绝不能/会在数据库表中无意复制.
To efficiently group the subarray data, you should implement temporary keys. cityId is a suitable value to group by -- because cityNames may intentionally duplicate in the future but cityId must never un/intentionally duplicate in your database table.
遇到每个新的cityId时，有条件的isset()调用将确定是否应存储一组新的/完整的数据，还是仅将数据附加到子数组.
When each new cityId is encountered, the conditional isset() call will determine whether a new/full set of data should be stored, or if data should merely be appended to the subarray.
我正在呼叫array_slice()，因为它减少了不必要的语法/代码膨胀.
I am calling array_slice() since it cuts down on unnecessary syntax / code-bloat.
遍历所有行后，可以为$result数组重新索引，将其嵌套在runBasedOnCity中，然后添加status元素.
After iterating through all of the rows, you can reindex the $result array, nest it inside runBasedOnCity, and add the status element.
我将使用PRETTY_PRINT演示我的演示，以便于阅读，但是在您的实际代码中，应该删除该参数.另外，请提个建议-尽量使变量名简短，以提高可读性.
I'll show my demo with PRETTY_PRINT so that it is easier to read, but in your actual code, you should remove the parameter. Also, a word of advice -- try to keep your variable names brief for improved readability.
代码:(演示)
$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));
输出:
'{
"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值后，下面是该解决方案:
After explaining how you wanted to preserve the id values in the subarrays, here is that solution:
代码:(演示)
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));
这篇关于使用PHP MySQL创建嵌套的JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
