开发ERP多标签搜索功能 Ajax POST请求提交后返回null

核心问题根因 #

返回null的问题主要由以下几个明确的错误导致：

• 服务端未初始化$data变量，当查询无结果时直接对未定义变量执行json_encode，返回null
• 使用@ROWNUM生成序号但未初始化该变量，导致SQL执行异常
• 直接拼接用户输入到SQL语句，未做转义，关键词含单引号、正则特殊符号时直接触发SQL语法错误，无合法返回值
• 前端读取的字段名和SQL查询返回的别名不匹配，JSON输出前可能存在PHP报错输出，都会破坏JSON格式导致前端解析为null
• 你写的if (data===null) {null.length='0'};是无效语法，会导致后续渲染逻辑异常

修复步骤 #

1. 服务端fetch_pd_list.php代码完全修复 #

<?php
// 此处保留你原有的数据库连接引入逻辑
// 第一步：初始化返回数组，避免未定义变量问题
$data = [];
// 第二步：初始化ROWNUM变量，否则序号生成逻辑失效
$conn->query("SET @ROWNUM := 0");

if(isset($_POST["query"]) && trim($_POST["query"]) !== '') {
    $searchRaw = $_POST["query"];
    // 替换逗号为正则或分隔符
    $search = str_replace(",", "|", $searchRaw);
    // 转义SQL特殊字符，避免注入和语法错误
    $search = $conn->real_escape_string($search);
    // 转义正则特殊字符，避免匹配异常
    $search = preg_quote($search);
    $query = "SELECT @ROWNUM := @ROWNUM + 1 AS no, A.*
                FROM 
                (SELECT product_no, brand_name, style_name, color_name, pd_image, concat(sg_id, '-', sg_col_id) AS sg_code, concat(cust_id, '-', cust_col_id) AS cust_code, production_type, size_type_name
                FROM 03_style
                RIGHT OUTER JOIN 03_product ON 03_style.style_no=03_product.style_no
                LEFT OUTER JOIN 02_brand ON 03_style.brand_no=02_brand.brand_no
                LEFT OUTER JOIN 03_shoe_color ON 03_product.color_no=03_shoe_color.shoe_color_no
                LEFT OUTER JOIN 03_production_type ON 03_style.pdtype_no=03_production_type.production_type_no
                LEFT OUTER JOIN 03_size_type ON 03_size_type.size_type_no=03_style.size_type_no
                ORDER BY brand_name, style_name, color_name) AS A
                WHERE brand_name REGEXP '".$search."'
                OR style_name REGEXP '".$search."'
                OR color_name REGEXP '".$search."'";
} else {
    $query = "SELECT @ROWNUM := @ROWNUM + 1 AS no, A.*
                FROM 
                (SELECT product_no, brand_name, style_name, color_name, pd_image, concat(sg_id, '-', sg_col_id) AS sg_code, concat(cust_id, '-', cust_col_id) AS cust_code, production_type, size_type_name
                FROM 03_style
                RIGHT OUTER JOIN 03_product ON 03_style.style_no=03_product.style_no
                LEFT OUTER JOIN 02_brand ON 03_style.brand_no=02_brand.brand_no
                LEFT OUTER JOIN 03_shoe_color ON 03_product.color_no=03_shoe_color.shoe_color_no
                LEFT OUTER JOIN 03_production_type ON 03_style.pdtype_no=03_production_type.production_type_no
                LEFT OUTER JOIN 03_size_type ON 03_size_type.size_type_no=03_style.size_type_no
                ORDER BY brand_name, style_name, color_name) AS A";
}

$statement = $conn->query($query);
if($statement) {
    while($row = mysqli_fetch_assoc($statement)) {
        $data[] = $row;
    }
}
// 强制指定JSON格式头，避免其他输出干扰解析
header('Content-Type: application/json');
echo json_encode($data);
exit;
?>

2. 前端代码逻辑优化 #

替换你原AJAX请求的success回调部分即可：

success: function(data){
    $('#total_records').text(data.length);
    var html = '';
    console.log(data);
    if (data.length > 0) {
        for(var count = 0; count < data.length; count++) {
            html += '<tr>';
            html += '<td>'+data[count].no+'</td>';
            html += '<td>'+data[count].brand_name+'</td>';
            html += '<td>'+data[count].style_name+'</td>';
            html += '<td>'+data[count].color_name+'</td>';
            html += '<td>'+data[count].pd_image+'</td>';
            html += '<td>'+data[count].sg_code+'</td>';
            html += '<td>'+data[count].cust_code+'</td>';
            html += '<td>'+data[count].production_type+'</td>';
            html += '<td>'+data[count].size_type_name+'</td></tr>'; 
        }
    } else {
            // 表格共9列，修正原colspan错误
            html = '<tr><td colspan="9">No Data Found</td></tr>';
    } 
    $('tbody').html(html);
}

调试提示 #

如果修复后还是异常，可以在服务端临时加var_dump($conn->error);打印SQL执行报错信息，确认表名、字段名无拼写错误，调试完成后删除调试代码即可。

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