实现MySQL表依赖列列表的搜索过滤器及mysqli_fetch_array报错解决

云上老周

2026-10-2

问题诊断

列选择功能失效
1. 列选择下拉框<select>放在<form>标签外部，表单提交时不会携带选中的列参数到后端
2. 后端变量$variable_php写死为0，没有接收前端传递的列选择值，也未做合法性校验，直接拼接进SQL会导致语法错误
空搜索触发报错
1. 只要点击Filter提交按钮，$_POST['search']就会被判定为存在，无论搜索框是否为空，都会进入筛选逻辑分支，不会触发你预想的else全表查询逻辑
2. 空搜索时搜索值为空，加上$variable_php是固定值0，生成的SQL语法非法，mysqli_query执行失败返回布尔值false，将false传入mysqli_fetch_array就触发了类型错误

修复方案

将列选择下拉框移动到<form>标签内部，确保提交时携带列参数
后端接收列选择参数后做白名单校验，仅允许预设的合法列名传入SQL，避免SQL注入风险
新增空值判断：搜索值为空、列参数非法时，直接返回全表数据
字符串类型的搜索值拼接进SQL时加单引号，同时做转义处理，避免语法错误和注入风险
新增查询结果校验，查询失败时兜底返回全表数据，避免报错
新增提交后表单值回填逻辑，优化使用体验

<!DOCTYPE html>
<html lang="en">
<?php
include "dbConn.php"; // 引入数据库连接文件

// 合法列白名单
$allowed_columns = ['r_num_module', 'r_int_fen', 'r_num_fen', 'r_pk'];
$variable_php = '';
$valueToSearch = '';

// 筛选逻辑
if (isset($_POST['search'])) {
    $valueToSearch = trim($_POST['valueToSearch']);
    $variable_php = trim($_POST['choice'] ?? '');
    
    // 校验列是否合法、搜索值非空才走筛选
    if (in_array($variable_php, $allowed_columns) && $valueToSearch !== '') {
        // 转义搜索值避免注入
        $valueToSearch = mysqli_real_escape_string($db, $valueToSearch);
        $query = "SELECT * FROM `Table2` WHERE `$variable_php` = '$valueToSearch'";
        $search_result = filterTable($query);
    } else {
        // 非法参数或空搜索返回全表
        $query = "SELECT * FROM `Table2`";
        $search_result = filterTable($query);
    }
} else {
    // 首次加载返回全表
    $query = "SELECT * FROM `Table2`";
    $search_result = filterTable($query);
}

// 执行查询函数
function filterTable($query)
{
    global $db; // 直接用引入的连接，避免重复建连
    $filter_Result = mysqli_query($db, $query);
    // 查询失败兜底返回全表
    if (!$filter_Result) {
        return mysqli_query($db, "SELECT * FROM `Table2`");
    }
    return $filter_Result;
}
?>
<body class="loggedin">
    <br><br>
    <br><br>
    <!-- 筛选表单 -->
    <form action="myfile.php" method="post">
        <label for="choice">Choose a column:</label>
        <select name="choice" id="id_choice">
            <option value="r_num_module" <?php if ($variable_php == 'r_num_module') echo 'selected'; ?>>r_num_module</option>
            <option value="r_int_fen" <?php if ($variable_php == 'r_int_fen') echo 'selected'; ?>>r_int_fen</option>
            <option value="r_num_fen" <?php if ($variable_php == 'r_num_fen') echo 'selected'; ?>>r_num_fen</option>
            <option value="r_pk" <?php if ($variable_php == 'r_pk') echo 'selected'; ?>>r_pk</option>
        </select>
        <input type="text" name="valueToSearch" placeholder="Search for a value" value="<?php echo htmlspecialchars($valueToSearch); ?>">
        <input type="submit" name="search" value="Filter">
        <br><br>
    </form>

    <table id="MyTable" class="table table-striped header-fixed">
        <thead>
            <tr>
                <th scope="col" style="text-align:center"><div class="cell-inner">id</div></th>
                <th scope="col" style="text-align:center"><div class="cell-inner">r_num_module</div></th>
                <th scope="col" style="text-align:center"><div class="cell-inner">r_int_fen</div></th>
                <th scope="col" style="text-align:center"><div class="cell-inner">r_num_fen</div></th>
                <th scope="col" style="text-align:center"><div class="cell-inner">r_pk</div></th>
            </tr>
        </thead>
        <tbody id="grid">
            <?php while($data = mysqli_fetch_array($search_result)): ?>
            <tr>
                <th scope="row"><div class="cell-inner"><?php echo $data['id']; ?></div></th>
                <td><div class="cell-inner"><?php echo $data['r_num_module']; ?></div></td>
                <td><div class="cell-inner"><?php echo $data['r_int_fen']; ?></div></td>
                <td><div class="cell-inner"><?php echo $data['r_num_fen']; ?></div></td>
                <td><div class="cell-inner"><?php echo $data['r_pk']; ?></div></td>
            </tr>
            <?php endwhile; ?>
        </tbody>
    </table>
    <?php mysqli_close($db); // 关闭连接 ?>
</body>
</html>

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