php 我在使用Oracle数据库构建带有选择下拉列表的搜索功能时遇到此错误

egdjgwm8 于 2022-11-21 发布在 PHP

关注(0)|答案(1)|浏览(107)

我得到这个错误，而建立搜索功能使用选择下拉。
SQL状态[HY 000]：一般错误：933 OCI统计执行：ORA-00933：SQL命令未正确结束（ext\pdo_oci\oci_statement.c：157）
注意：未定义的变量：第87行的结果
警告：为第87行中的foreach（）提供的参数无效

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

$sql = 'SELECT * 
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE = 20221104';

    try{
      $params = [];
      if(isset($_GET['group']) && $_GET['group']){
        $sql = $sql. 'WHERE GROUP_NAME=:GROUP_NAME';
        $params= [
          'GROUP_NAME' => $_GET['group']
        ];
      }
      $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 GROUP_NAME FROM SFISM4.R_STATION_ATE_T GROUP BY GROUP_NAME ORDER BY GROUP_NAME ASC');
      $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">
  </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 DETAIL</h2>
          </div>

          <form method="get">

            <input type="text" name="groupname" class="col-sm-2" placeholder="Model name/Group name" value="<?= (isset($_GET['groupname']) && $_GET['groupname']) ? $_GET['groupname'] : ''  ?>">

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

            </select>

            <button class="btn btn-success btn-primary" type="submit" name="filter" id="filter" style="">
                <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>WIP_QTY</th>
                <th>PASS_QTY</th>
                <th>FIRST_FAIL_QTY</th>
                <th>FAIL_QTY</th>
                <th>RETEST_QTY</th>
                <th>REPASS_QTY</th>
                <th>LINE_NAME</th>
              </tr>
              <?php
                foreach($result as $key => $value):
              ?>
              <tr>
                <td> <?=$value['MODEL_NAME']?></td>
                <td> <?php echo $value['GROUP_NAME']; ?>
                <td> <?=$value['STATION_NAME']; ?>
                <td> <?=$value['WIP_QTY']; ?>
                <td> <?=$value['PASS_QTY']; ?>
                <td> <?=$value['FIRST_FAIL_QTY']; ?>
                <td> <?=$value['FAIL_QTY']; ?>
                <td> <?=$value['RETEST_QTY']; ?>
                <td> <?=$value['REPASS_QTY']; ?>
                <td> <?=$value['LINE_NAME'] ?>
              </tr>
              <?php endforeach; ?>
            </thead>
          </table>

        </div>
      </div>
    </div>

  </body>
</html>

php

来源：https://stackoverflow.com/questions/74327053/i-got-this-error-while-building-the-search-function-with-select-dropdown-using-o

1条答案

按热度按时间

piv4azn71#

对于使用$_GET ['group']的情况，sql将具有DOUBLE“where”关键字。
因此，请更改

$sql = $sql. 'WHERE GROUP_NAME=:GROUP_NAME';

至

$sql = $sql. ' AND GROUP_NAME=:GROUP_NAME';

另一方面，如果系统提示WORK_DATE为VARCHAR数据类型，则表示WORK_DATE不是数值类型，下行将失败：

$sql = 'SELECT * FROM SFISM4.R_STATION_ATE_T WHERE WORK_DATE = 20221104';

您可以用括号将数据20221104括起来，也可以改为将此字段作为另一个参数放在预准备语句中.
因此，建议变更为：
更改块

$sql = 'SELECT * 
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE = 20221104';

    try{
      $params = [];
      if(isset($_GET['group']) && $_GET['group']){
        $sql = $sql. 'WHERE GROUP_NAME=:GROUP_NAME';
        $params= [
          'GROUP_NAME' => $_GET['group']
        ];
      }
     //.... rest of the code

至

// For example, $work_date = '20221104'; 
$work_date = '20221104';

$sql = 'SELECT * 
FROM SFISM4.R_STATION_ATE_T ';

    try{
      $params = [];

      if(isset($_GET['group']) && $_GET['group']){

        $sql = $sql. ' WHERE WORK_DATE =:WORK_DATE AND GROUP_NAME=:GROUP_NAME';
        $params= [
          'WORK_DATE' => $work_date,
          'GROUP_NAME' => $_GET['group']
        ];

      } else {
        $sql = $sql. ' WHERE WORK_DATE =:WORK_DATE '; 
        $params= [
          'WORK_DATE' => $work_date
        ];

      }
     //.... rest of the code

[附加点]

如果要自动使用“今天的日期”，则
变更

$work_date = '20221104';

至

$work_date=date('Ymd');

或者，也可以使用以下代码（由Thang Đai提供）：

SYSDATE: WORK_DATE= TO_CHAR(SYSDATE, 'YYYYMMDD')

赞(0）回复(0）举报 2022-11-21

我来回答

php 我在使用Oracle数据库构建带有选择下拉列表的搜索功能时遇到此错误

1条答案

相关问题

热门标签

最新问答