业务场景:
在做首页统计图场景的时候,针对折线图,接口需要提供给前台这样的数据格式:
##解决:
首先 从数据库中根据日期捞数据,比如2019-07-01 到2019-07-10,如下图
可以看到 6号7号8号和10号是没有记录的,但是前台需要每一天的记录,缺失的日期total为0,这样才合理,这里有两种解决办法,一种是在sql查询的啥时候创建连续的日期辅表来连表查询。另一种是php处理,遍历date的所有日期与数据库得到的数据相比较,没有的补0。这里采用第二种。
1.列出日期区间内的所有日期
$start_time=strtotime($start_time);
$end_time=strtotime($end_time);
$date = [];
for ($start_time; $start_time < $end_time; $start_time += 86400) {
$date[] = date('Y-m-d', $start_time);
}
2.日期与数据库数据遍历比较
从折线图中可以看出,折线图是多个的,所以数据库的数据也是多维的,格式如下:
$robot[] = [
"name" => "机器人1",
"id" => 1,
"lists" => [
"0" => ['count' => 4, 'newdate' => '2019-07-01'],
"1" => ['count' => 1, 'newdate' => '2019-07-02'],
"2" => ['count' => 5, 'newdate' => '2019-07-03'],
"3" => ['count' => 1, 'newdate' => '2019-07-05'],
"4" => ['count' => 1, 'newdate' => '2019-07-07']
]
];
$robot[] = [
"name" => "机器人2",
"id" => 2,
"lists" => [
"0" => ['count' => 1, 'newdate' => '2019-07-01'],
"1" => ['count' => 3, 'newdate' => '2019-07-04'],
"2" => ['count' => 1, 'newdate' => '2019-07-05'],
"3" => ['count' => 4, 'newdate' => '2019-07-06'],
"4" => ['count' => 1, 'newdate' => '2019-07-10']
]
];
$robot[] = [
"name" => "机器人3",
"id" => 3,
"lists" => []
];
解决思路:
把lists里的newdate取出来当做key:
foreach ($robot_data as $key=>$value){
$value['lists']=array_column($robot_count,null,'newdate');
$robot_result[$key]=$value;
}
得到这样的:
$robot[] = [
"name" => "机器人1",
"id" => 1,
"lists" => [
"2019-07-01" => ['count' => 4, 'newdate' => '2019-07-01'],
"2019-07-02" => ['count' => 1, 'newdate' => '2019-07-02'],
"2019-07-03" => ['count' => 5, 'newdate' => '2019-07-03'],
"2019-07-05" => ['count' => 1, 'newdate' => '2019-07-05'],
"2019-07-07" => ['count' => 1, 'newdate' => '2019-07-07']
]
];
$robot[] = [
"name" => "机器人2",
"id" => 2,
"lists" => [
"2019-07-01" => ['count' => 1, 'newdate' => '2019-07-01'],
"2019-07-04" => ['count' => 3, 'newdate' => '2019-07-04'],
"2019-07-05" => ['count' => 1, 'newdate' => '2019-07-05'],
"2019-07-06" => ['count' => 4, 'newdate' => '2019-07-06'],
"2019-07-10" => ['count' => 1, 'newdate' => '2019-07-10']
]
];
$robot[] = [
"name" => "机器人3",
"id" => 3,
"lists" => []
];
然后拿date和lists遍历比较
foreach ($robot as $key => &$value) {
foreach ($date as $b) {
if (empty($value['lists'][$b])) {
$value['lists'][$b] = [
'total' => 0,
'newdate' => $b
];
}
}
}
外面循环数据库中的数据,里面循环date,如果date不是lists的key,那么就自动补0.数据如下:
$robot[] = [
"name" => "机器人1",
"id" => 1,
"lists" => [
"2019-07-01" => ['count' => 4, 'newdate' => '2019-07-01'],
"2019-07-02" => ['count' => 1, 'newdate' => '2019-07-02'],
"2019-07-03" => ['count' => 5, 'newdate' => '2019-07-03'],
"2019-07-05" => ['count' => 1, 'newdate' => '2019-07-05'],
"2019-07-07" => ['count' => 1, 'newdate' => '2019-07-07'],
"2019-07-04" => ['count' => 0, 'newdate' => '2019-07-04'],
"2019-07-06" => ['count' => 0, 'newdate' => '2019-07-06'],
"2019-07-07" => ['count' => 0, 'newdate' => '2019-07-07'],
"2019-07-09" => ['count' => 0, 'newdate' => '2019-07-09],
"2019-07-10" => ['count' => 0, 'newdate' => '2019-07-010'],
]
];
$robot[] = [
"name" => "机器人2",
"id" => 2,
"lists" => [
"2019-07-01" => ['count' => 1, 'newdate' => '2019-07-01'],
"2019-07-04" => ['count' => 3, 'newdate' => '2019-07-04'],
"2019-07-05" => ['count' => 1, 'newdate' => '2019-07-05'],
"2019-07-06" => ['count' => 4, 'newdate' => '2019-07-06'],
"2019-07-10" => ['count' => 1, 'newdate' => '2019-07-10']
"2019-07-02" => ['count' => 3, 'newdate' => '2019-07-02'],
"2019-07-03" => ['count' => 1, 'newdate' => '2019-07-03'],
"2019-07-07" => ['count' => 4, 'newdate' => '2019-07-07'],
"2019-07-08" => ['count' => 1, 'newdate' => '2019-07-08']
"2019-07-09" => ['count' => 4, 'newdate' => '2019-07-09'],
]
];
$robot[] = [
"name" => "机器人3",
"id" => 3,
"lists" => [
"2019-07-01" => ['count' => 4, 'newdate' => '2019-07-01'],
"2019-07-02" => ['count' => 1, 'newdate' => '2019-07-02'],
"2019-07-03" => ['count' => 5, 'newdate' => '2019-07-03'],
"2019-07-04" => ['count' => 1, 'newdate' => '2019-07-04'],
"2019-07-05" => ['count' => 1, 'newdate' => '2019-07-05'],
"2019-07-06" => ['count' => 0, 'newdate' => '2019-07-06'],
"2019-07-07" => ['count' => 0, 'newdate' => '2019-07-07'],
"2019-07-08" => ['count' => 0, 'newdate' => '2019-07-08'],
"2019-07-09" => ['count' => 0, 'newdate' => '2019-07-09],
"2019-07-10" => ['count' => 0, 'newdate' => '2019-07-10'],]
];
虽然缺失的日期补上了,但是顺序乱了,根据key 日期排下序 留下count合并数组 释放掉不用的
foreach ($robot_result as $key=>&$value){
ksort($value['lists']);//按照日期排序
$value['arr']=array_column($value['lists'],'count');
unset($value['id'],$value['lists'],$value['newdate']);//释放 只留下留下name和arr
}
ok 大功告成!
``到这里还看不懂的话 给你数据自己测试下:
<?php
/**
* Created by PhpStorm.
* User: xiaoman
* Date: 2019/7/12
* Time: 16:47
*/
$robot[] = [
"name" => "机器人1",
"id" => 1,
"lists" => [
"2019-07-01" => ['count' => 4, 'newdate' => '2019-07-01'],
"2019-07-02" => ['count' => 1, 'newdate' => '2019-07-02'],
"2019-07-03" => ['count' => 5, 'newdate' => '2019-07-03'],
"2019-07-05" => ['count' => 1, 'newdate' => '2019-07-05'],
"2019-07-07" => ['count' => 1, 'newdate' => '2019-07-07']
]
];
$robot[] = [
"name" => "机器人2",
"id" => 2,
"lists" => [
"2019-07-01" => ['count' => 1, 'newdate' => '2019-07-01'],
"2019-07-04" => ['count' => 3, 'newdate' => '2019-07-04'],
"2019-07-05" => ['count' => 1, 'newdate' => '2019-07-05'],
"2019-07-06" => ['count' => 4, 'newdate' => '2019-07-06'],
"2019-07-11" => ['count' => 1, 'newdate' => '2019-07-11']
]
];
$robot[] = [
"name" => "机器人3",
"id" => 3,
"lists" => []
];
$start_time = strtotime("2019-07-01");
$end_time = strtotime("2019-07-15 23:59:59");
$date = [];
for ($start_time; $start_time < $end_time; $start_time += 86400) {
$date[] = date('Y-m-d', $start_time);
}
foreach ($robot as $key => &$value) {
foreach ($date as $b) {
if (empty($value['lists'][$b])) {
$value['lists'][$b] = [
'count' => 0,
'date' => $b
];
}
}
}
foreach ($robot as $key=>&$value){
ksort($value['lists']);
$value['arr']=array_column($value['lists'],'count');
unset($value['id'],$value['lists'],$value['newdate']);
}
echo "<pre>";print_r($robot);echo "<pre>";
die;