基于PHP+Oracle数据库的下拉框搜索功能实现求助

原代码存在的问题 #

• 过滤逻辑受限：仅当MODEL和GROUP同时选择时才触发过滤，无法单独搜索其中一项
• SQL语法错误：拼接AND条件时未加空格，会生成WORK_DATE='20240520'AND MODEL_NAME='XXX'这类数据库无法解析的语句
• HTML标签不完整：表格中多个<td>标签缺少闭合标签，导致页面渲染异常
• 图标依赖缺失：按钮使用的Font Awesome图标未引入对应样式，无法正常显示

修正后的完整代码 #

<?php
include '../includes/dbconnection.php';

$sql = "SELECT * 
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE= TO_CHAR(SYSDATE, 'YYYYMMDD')"; 
$params = [];
$conditions = [];

try{
    // 单独判断MODEL_NAME参数
    if(isset($_GET['model']) && trim($_GET['model']) !== ''){
        $conditions[] = 'MODEL_NAME=:MODEL_NAME';
        $params['MODEL_NAME'] = trim($_GET['model']);
    }
    // 单独判断GROUP_NAME参数
    if(isset($_GET['group']) && trim($_GET['group']) !== ''){
        $conditions[] = 'GROUP_NAME=:GROUP_NAME';
        $params['GROUP_NAME'] = trim($_GET['group']);
    }
    // 拼接额外过滤条件
    if(!empty($conditions)){
        $sql .= ' AND ' . implode(' AND ', $conditions);
    }
    $stm = $db->prepare($sql);
    $stm->execute($params);
    $result = $stm->fetchAll(PDO::FETCH_ASSOC);
}catch(PDOException $e){
    echo $e->getMessage();
}

try{
    $stm = $db->prepare('SELECT DISTINCT MODEL_NAME FROM SFISM4.R_STATION_ATE_T ');
    $stm->execute();
    $result_model = $stm->fetchAll(PDO::FETCH_OBJ);
}catch(PDOException $e){
    echo $e->getMessage();
}

try{
    $stm = $db->prepare('SELECT DISTINCT GROUP_NAME FROM SFISM4.R_STATION_ATE_T');
    $stm->execute();
    $result_group = $stm->fetchAll(PDO::FETCH_OBJ);
}catch(PDOException $e){
    echo $e->getMessage();
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Station Detail</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <!-- 引入Font Awesome样式，修复图标显示问题 -->
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-sm-12">
                <div class="well" style="background-color:LightGray;">
                    <h2 class="text-center">Station Details</h2>
                </div>

                <form method="get">
                    <select name="model" id="model">
                        <option value="">- MODEL_NAME -</option>
                        <?php foreach($result_model as $key => $value):?>
                            <option value="<?=htmlspecialchars($value->MODEL_NAME)?>" <?= (isset($_GET['model']) && $_GET['model'] == $value->MODEL_NAME) ? 'selected' : '' ?>><?=htmlspecialchars($value->MODEL_NAME)?></option>
                        <?php endforeach; ?>
                    </select>

                    <select name="group" id="group">
                        <option value="">- GROUP_NAME -</option>
                        <?php foreach($result_group as $key => $value):?>
                            <option value="<?=htmlspecialchars($value->GROUP_NAME)?>" <?= (isset($_GET['group']) && $_GET['group'] == $value->GROUP_NAME) ? 'selected' : '' ?>><?=htmlspecialchars($value->GROUP_NAME)?></option>
                        <?php endforeach; ?>
                    </select>

                    <button class="btn btn-success btn-primary" type="submit" name="filter" id="filter">
                        <i class="fa fa-filter"></i> Filter
                    </button>
                </form>          
                
                <br/><br/>

                <table class="table table-hover table-striped table-bordered" cellspacing="0" width="100%">
                    <thead>
                        <tr>
                            <th>MODEL_NAME</th>
                            <th>GROUP_NAME</th>
                            <th>STATION_NAME</th>
                            <th>WORK_DATE</th>
                            <th>PASS_QTY</th>
                            <th>FAIL_QTY</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach($result as $key => $value):?>
                            <tr>
                                <td><?=htmlspecialchars($value['MODEL_NAME'])?></td>
                                <td><?=htmlspecialchars($value['GROUP_NAME'])?></td>
                                <td><?=htmlspecialchars($value['STATION_NAME'])?></td>
                                <td><?=htmlspecialchars($value['WORK_DATE'])?></td>
                                <td><?=htmlspecialchars($value['PASS_QTY'])?></td>
                                <td><?=htmlspecialchars($value['FAIL_QTY'])?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</body>
</html>

修正说明 #

1. 灵活过滤逻辑：分别判断两个参数的存在性，支持单独搜索MODEL、单独搜索GROUP，或同时搜索两者
2. 修复SQL语法：拼接条件时添加空格，确保SQL语句格式合法
3. 规范HTML结构：补全所有闭合标签，将数据行移至<tbody>内，符合HTML标准
4. 完善图标显示：引入Font Awesome样式文件，让过滤按钮图标正常展示
5. 增加安全防护：用htmlspecialchars()转义输出内容，避免XSS攻击

内容的提问来源于stack exchange，提问作者Thang Đai