添加日期筛选后Chart JS折线图AJAX请求失效求助

我在给基于Chart JS的折线图开发日期筛选功能，添加日期范围参数后图表完全不工作，控制台也没报错。现有三个PHP文件：SAP连接管理文件、AJAX请求处理文件、包含图表的首页。之前没加日期筛选时功能正常，单独调用查询函数也能正常执行，但AJAX没返回有效错误信息。

index.php中的AJAX请求脚本 #

$(document).ready(function(){
    $('#selector-ceco').on('change', function(){

        // -------------variables actualizacion graficas -----------
        //console.log($('#fecha_i').val(), typeof $('#fecha_i').val());
        var ceco = String($('#selector-ceco').val());
        var fecha_i = $('#fecha_i').val().replace(/-/g, '');
        var fecha_f = $('#fecha_f').val().replace(/-/g, '');
        //if (ceco==0) {ceco="";};
        console.log(typeof fecha_f, fecha_f);

        var montos = []; var fechas = []; var gasto_total = 0; var dates =[];
        
        $.ajax({
            url: "inc/cargar_hist_cecos.php",
            type: 'POST',
            data: {'ceco_electo':ceco, 'fecha_i': fecha_i, 'fecha_f': fecha_f},
            dataType: 'json',
            success: function(retorno, status){
                console.log("llegue");
                for (let i = 0 ; i<retorno.length ; i++){
                    montos.push(parseFloat(retorno[i]['TOTAL']));
                    fechas.push(retorno[i]['FECHA'].replace(/(\d\d\d\d)(\d\d)/,'$1-$2'));
                    //dates.push(Date(retorno[i]['FECHA']));
                    gasto_total += parseFloat(retorno[i]['TOTAL']);
                };
                
                //console.log(typeof dates[0], Date(fechas[0]));
                
                gasto_ceco.data.datasets[0].data = montos;
                gasto_ceco.data.labels = fechas;
                gasto_ceco.update();
            }
        });

AJAX请求处理文件（inc/cargar_hist_cecos.php） #

<?php
include_once 'db.inc.php';

session_start();

if(isset($_POST['ceco_electo'])){
    $CONN_data = new DbS;
    
    $valores = $CONN_data->gasto_ceco( $_POST['ceco_electo'] , $_SESSION['grupo'], $_POST['fecha_i'], $_POST['fecha_f']);

    $hist = json_encode($valores);
    echo $hist;
}else{
    echo "error";
};
  
?>

SAP连接文件中的gasto_ceco函数 #

public function gasto_ceco ($CECO, $tipo, $fecha_i, $fecha_f){
    $pdo = $this->connect_sap();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = <<<SQL
            SELECT T2."OcrCode" as "CECO", sum(T2."Quantity"*T2."Price") as "TOTAL", YEAR(T4."DocDate")*100+MONTH(T4."DocDate") as FECHA
            FROM PCH1 T2 INNER JOIN OPCH T4 on T4."DocEntry"=T2."DocEntry"
            WHERE T2."AcctCode" like '55%' AND T4."DocDate" > '20220101' AND T4."CANCELED" <> 'N ' AND T2."OcrCode" = {$CECO} AND T4."DocDate" > cast({$fecha_i} as VARCHAR) AND T4."DocDate" < CAST({$fecha_f} as VARCHAR)
            GROUP BY T2."OcrCode", YEAR(T4."DocDate")*100+MONTH(T4."DocDate")
            ORDER BY YEAR(T4."DocDate")*100+MONTH(T4."DocDate") ASC 
    SQL;
    
    
    echo $sql;
    $stmt = $pdo->prepare($sql);
    
    $res = $stmt->execute();
    
    echo $res;
    
    try {
        return $gasto_ceco = $stmt->fetchAll(PDO::FETCH_ASSOC); 
    }catch(PDOException $e){
        echo "error: ".$e->getMessage();
    };
}

图表HTML代码（index.php中） #

<div class="card-body">
    <div class="chart-area">
        <input type="date" id="fecha_i" value="2022-01-01" name="fecha_i">
        <input type="date" id="fecha_f" value="2023-12-31" name="fecha_f">
        <canvas id="grafico-gasto-ceco"></canvas>
        
        <script>
            var labels= []; var data_gasto_raw = [];
            var ctx_gasto_ceco = document.getElementById("grafico-gasto-ceco").getContext('2d');
            var config = {
                type: 'line',
                data: {
                    labels: labels,
                    datasets: [{
                        data: data_gasto_raw,
                        backgroundColor: 'rgba(78, 115, 223, 0.05)',
                        lineTension: 0.3,
                        pointRadius: 3,
                        pointBackgroundColor: "rgba(78, 115, 223, 1)",
                        pointBorderColor: "rgba(78, 115, 223, 1)",
                        pointHoverRadius: 3,
                        pointHoverBackgroundColor: "rgba(78, 115, 223, 1)",
                        pointHoverBorderColor: "rgba(78, 115, 223, 1)",
                        pointHitRadius: 10,
                        pointBorderWidth: 2,
                        
                    }],
                options: {responsive:true,
                         layout: {
                              padding: {
                                left: 10,
                                right: 25,
                                top: 25,
                                bottom: 20
                              }
                            },
                          scales: {
                              xAxes: [{
                                time: {
                                  unit: 'date'
                                },
                                gridLines: {
                                  display: false,
                                  drawBorder: false
                                },
                                ticks: {
                                  maxTicksLimit: 7
                                }
                              }],
                              yAxes: [{
                                ticks: {
                                  maxTicksLimit: 5,
                                  padding: 10,
                                  // Include a dollar sign in the ticks
                                  callback: function(value, index, values) {
                                    return '$' + number_format(value);
                                  }
                                },
                                gridLines: {
                                  color: "rgb(234, 236, 244)",
                                  zeroLineColor: "rgb(234, 236, 244)",
                                  drawBorder: false,
                                  borderDash: [2],
                                  zeroLineBorderDash: [2]
                                }
                              }],
                            },
                            legend: {display: false},
                            tooltips: {
                                  backgroundColor: "rgb(255,255,255)",
                                  bodyFontColor: "#858796",
                                  titleMarginBottom: 10,
                                  titleFontColor: '#6e707e',
                                  titleFontSize: 14,
                                  borderColor: '#dddfeb',
                                  borderWidth: 1,
                                  xPadding: 15,
                                  yPadding: 15,
                                  displayColors: false,
                                  intersect: false,
                                  mode: 'index',
                                  caretPadding: 10
                                }
                         }
                }
            };
            var gasto_ceco = new Chart(ctx_gasto_ceco, config);
        </script>
    </div>
</div>

排查方向 #

1. 修复AJAX响应格式：gasto_ceco函数里的echo $sql;和echo $res;会输出额外内容，污染JSON响应，导致前端无法解析。直接删掉这两行，确保只有echo $hist;输出纯JSON。
2. 修正SQL日期比较逻辑：
   • 确认T4."DocDate"的字段类型，如果是日期类型，不要用字符串转换比较，直接传入日期格式参数（比如YYYY-MM-DD）。
   • 原SQL里的T4."DocDate" > '20220101'和新添加的日期条件可能冲突，检查范围是否覆盖需求。
3. 添加AJAX错误回调：补全错误处理逻辑，查看请求是否真的成功：

   error: function(xhr, status, error) {
       console.log(xhr.responseText);
       console.log(status, error);
   }
   

4. 验证参数传递：在cargar_hist_cecos.php开头添加var_dump($_POST);，确认fecha_i和fecha_f是否正确接收。
5. 修复SQL注入风险：改用参数绑定代替直接拼接变量，避免语法错误和安全问题：

   $sql = <<<SQL
           SELECT T2."OcrCode" as "CECO", sum(T2."Quantity"*T2."Price") as "TOTAL", YEAR(T4."DocDate")*100+MONTH(T4."DocDate") as FECHA
           FROM PCH1 T2 INNER JOIN OPCH T4 on T4."DocEntry"=T2."DocEntry"
           WHERE T2."AcctCode" like '55%' AND T4."DocDate" > '20220101' AND T4."CANCELED" <> 'N ' AND T2."OcrCode" = ? AND T4."DocDate" > ? AND T4."DocDate" < ?
           GROUP BY T2."OcrCode", YEAR(T4."DocDate")*100+MONTH(T4."DocDate")
           ORDER BY YEAR(T4."DocDate")*100+MONTH(T4."DocDate") ASC 
   SQL;
   $stmt = $pdo->prepare($sql);
   $stmt->execute([$CECO, $fecha_i, $fecha_f]);
   

6. 检查返回数据格式：在AJAX的success回调里打印console.log(retorno)，确认返回的数组是否为空，TOTAL和FECHA字段是否符合预期。

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