使用 PHP 和 MySQL 查询为一系列数据创建 JSON
我正在尝试使用 JSON 数据创建条形图，并且需要将格式更改为月份系列，但我对如何做到这一点感到困惑。请帮助我如何解决这个问题。
当前 JSON 数据：
{ “数据”： [
{
“名称”：“一月”，
“设施”：“讨论室”，
“价值”：22
},
{
“名称”：“一月”，
“设施”：“卡雷尔室”，
“价值”：102
},
{
“名称”：“一月”，
“设施”：“储物柜”，
“价值”：5
},
{
“名称”：“二月”，
“设施”：“讨论室”，
“价值”：86
},
{
“名称”：“二月”，
“设施”：“卡雷尔室”，
“价值”：155
},
{
“名称”：“Mac”，
“设施”：“卡雷尔室”，
“价值”：224
},
{
“名称”：“Mac”，
“设施”：“储物柜”，
“价值”：3
},
]
}
所需的 JSON 数据：
{ “数据”： [
{
“名称”：“一月”，
“系列”：[
{ "name":"讨论室","value": 22},
{ "name": "Carrel Room", "value": 102},
{“名称”：“储物柜”，“价值”：5}，
]
“名称”：“二月”，
“系列”：[
{ "name":"讨论室","value": 86},
{ "name": "Carrel Room", "value": 155},
{“名称”：“储物柜”，“价值”：0}，
]
“名称”：“三月”，
“系列”：[
{ "name":"讨论室","value": 0},
{ "name": "Carrel Room", "value": 224},
{“名称”：“储物柜”，“价值”：3}，
]
]
}
我的代码：
$db = mysqli_connect($host, $user, $pass, $database) or die("you did not connect");
header('Access-Control-Allow-Origin: *');
header("Access-Control-Allow-Credentials: true");
header('Access-Control-Allow-Methods: GET');
header('Access-Control-Max-Age: 1000');
header('Access-Control-Allow-Headers: Origin, Content-Type');
$facility =($_GET['year']);
$query = "select monthname(datetime) as 'name',
case
when all_items.itype = '127' then 'Discussion Room'
when all_items.itype = '126' then 'Carrel Room'
when all_items.itype = '121' then 'Locker'
else '0'
end as 'facility',
count(*) AS 'value'
from statistics
left join (
select itemnumber, itype from deleteditems
union
select itemnumber, itype from items
) as all_items USING (itemnumber)
where all_items.itype in (127, 126, 121) and
statistics.type = 'issue' and
year(statistics.datetime) = '$facility'
group by month(datetime), all_items.itype desc";
$result = mysqli_query($db, $query)or die(mysqli_error());
$response = array();
$posts = array();
米琪卡哇伊2回答
-
弑天下
您应该更改查询数据库的方式。您应该使用准备好的语句，而不是在查询中注入字符串。这使您容易受到 sql 注入的影响。阅读有关准备好的语句的更多信息。回到问题：对于发现的每个月，您需要创建一个数组，其中包含一个系列数组。检查月份数组是否存在，如果它不创建它并创建系列数组。然后创建一个包含系列数据的数组，并将其中的系列数据推送到该月的系列数组中。查看代码要容易得多。我也添加了评论。$result = mysqli_query($db, $query)or die(mysqli_error());$response = array();$posts = array();while($row=$result->fetch_assoc()) { $month=$row['name']; if(!isset($posts[$month])){// Check to see if the month array exists //if it doesn't create it $posts[$month] = array(); //create the series array so data can be pushed to it $posts[$month]["series"] = array(); } //check to see if it is null and if it is make it 0 if(is_null($row['value'])){ $row['value'] =0; } //put the series data in the correct format with correct names $series_array = array( "name" => $row['facility'], "value" => $row['value'] ); //push the series data into the months series array array_push($posts[$month]["series"],$series_array);}// put months and series together $new_posts=array();foreach($posts as $month_name => $data){ array_push($new_posts, array("name"=> $month_name, "series"=> $data['series'] ));}$posts = $new_posts;$response['data'] = array($posts);header('Content-Type: application/json');echo json_encode($response, JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT); -
素胚勾勒不出你
这是您需要的代码$result = mysqli_query($db, $query)or die(mysqli_error($db));$response = array();$posts = array();while($row=$result->fetch_assoc()){ $month = $row['name']; $value = $row['value']; if(empty($value)){ $value = 0; } $series = array( "name" => $row['facility'], "value" => $value ); $found_key = array_search($month, array_column($posts, 'name'));// check if month value exist in name key if(!$found_key){//if not found $found_key = sizeof($posts);// get the size of array which will be 1 more then last index of array } $posts[$found_key]['name'] = $month;// this add name of month if(!isset($posts[$found_key]['series'])){ // check if series is set $posts[$found_key]['series'] = array();// or set it } $posts[$found_key]['series'][] = $series; //assign the series array}$response['data'] = $posts;header('Content-Type: application/json');echo json_encode($response, JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT);
随时随地看视频慕课网APP
PHP