PHP生成嵌套小时统计的日统计JSON出现数组重复如何解决

问题根源 #

• $data2数组没有在每次处理新日期前重置，每次循环日期时都会把当日小时数据追加到之前的$data2数组末尾，导致后续日期的子数组一直包含前面所有日期的小时统计数据。

修复方案 #

每次进入外层日期处理循环时，先将$data2重置为空数组，保证每个日期的小时统计都是独立的新数组。

修改后的完整代码 #

$sql = "SELECT cs_id, dated, count(p_id) as day_total FROM post_logs
where cs_id = '$cs_id' and dated BETWEEN '2021-06-01' and '2021-06-30'
group by dated order by dated";

$result = mysqli_query($con, $sql);  
while($row =  mysqli_fetch_array($result))  { 
    // 关键修改：每次处理新日期前清空小时统计数组
    $data2 = [];
    $dated = $row['dated'];      
    $cs_id = $row['cs_id'];     
    $dt = $row['day_total'];
      
    $sql2 = "SELECT dated, date_format(actual,'%H:00') as hours, count(p_id) as hour_total 
    FROM post_logs where cs_id = '$cs_id' and dated = '$dated' group by date_format(actual,'%H') order by dated"; 

    $result2 = mysqli_query($con, $sql2);  
    while($row2 = mysqli_fetch_assoc($result2)) {
       $data2[] = $row2;
    } //r2
      
    $data[] = array('date_id' => sha1($dated), 'name' => $dated, 'day_total' => $dt, 'data2' => $data2);    
} //r1
echo json_encode($data);

可选优化建议 #

• 可以通过单次SQL按dated和小时分组查询所有统计数据，再在PHP中按日期分组拼装，减少嵌套数据库查询次数，提升性能。
• 建议使用参数绑定替代字符串拼接SQL，避免SQL注入风险。

内容的提问来源于stack exchange，提问作者noob_abm