如何使用Google API、PHP与MySQL修复饼图日期间隔筛选功能

现有代码问题 #

• 查询逻辑顺序错误：筛选后的查询逻辑放在饼图渲染代码之后，筛选出的新数据无法传入前端图表生成逻辑
• 筛选后查询规则错误：没有保留原有的按部门聚合工时的逻辑，返回的原始工单数据无法直接用于饼图生成
• 循环变量命名错误：foreach($data as $data)会直接覆盖整个数据集数组，导致只能输出第一条数据
• 前端容器重复：筛选后循环输出了多个id="piechart"的div，重复ID会导致图表渲染失败
• 存在SQL注入风险：直接将用户传入的日期参数拼接到SQL语句中，没有使用PDO参数绑定

修正方案 #

调整数据查询逻辑，优先判断是否传入日期参数，统一返回饼图所需的部门工时聚合数据，删除重复的容器输出逻辑，修正后代码如下：

<?php   
// 优先处理数据查询，判断是否传入筛选日期
try {
    if (isset($_GET['from_date2']) && isset($_GET['to_date2'])) {
        // 带日期筛选的聚合查询，使用参数绑定防注入
        $query = $connection->prepare("SELECT Department, SUM(workHrs) as number FROM serviceapplication WHERE CreatedOn BETWEEN :from AND :to GROUP BY Department");
        $query->bindParam(':from', $_GET['from_date2']);
        $query->bindParam(':to', $_GET['to_date2']);
    } else {
        // 无筛选时的全量聚合查询
        $query = $connection->prepare("SELECT Department, SUM(workHrs) as number FROM serviceapplication GROUP BY Department");
    }
    $query->execute();
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $data = $query->fetchAll();
} catch (PDOException $e) {
    echo $e->getMessage();
}
?> 
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> 
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);

function drawChart() {
    var data = google.visualization.arrayToDataTable([
      ['Department', 'Work Hours'],
      <?php
          // 修正循环变量命名，避免覆盖原数组
          foreach($data as $item) :     
            echo "['".$item["Department"]."', ".$item["number"]."],";
          endforeach; 
          ?>
    ]);
    
    var options = {
        backgroundColor: "none",
        title: '',
        width: 900,
        height: 500,
        pieHole: 0.5,
        colors: ['#4c325c', '#8ea5cc', '#aa579f', '#391d9d', '#fcbd9c', '#dc346c'],
    };
    
    var chart = new google.visualization.PieChart(document.getElementById('piechart'));
    chart.draw(data, options);
}
</script>
</head>
<body>
<form action="" method="GET">
    <label class="text-secondary">起始日期</label>
    <input type="date" name="from_date2" value="<?php if(isset($_GET['from_date2'])){ echo $_GET['from_date2']; } ?>" class="form-control">

    <label class="text-secondary">结束日期</label>
    <input type="date" name="to_date2" value="<?php if(isset($_GET['to_date2'])){ echo $_GET['to_date2']; } ?>" class="form-control">
          
    <label class="text-secondary">筛选</label> <br>
    <button type="submit" class="btn btn-primary">筛选</button>
 </form>
<br>
<!-- 固定一个饼图容器即可，无需循环输出 -->
<div id="piechart"></div>
</body>

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