php - I got this error while building the search function with select dropdown using Oracle database

I am getting this error while building the search function using select dropdown.

SQLSTATE[HY000]: General error: 933 OCIStmtExecute: ORA-00933: SQL command not properly ended (ext\pdo_oci\oci_statement.c:157)

Notice: Undefined variable: result in C:\xampp\htdocs\test_Oracle\src\index.php on line 87

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\test_Oracle\src\index.php on line 87

<?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>

Answer

Solution:

For the case where you are using $_GET['group'], the sql will have DOUBLE "where" keyword.

Hence, please change

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

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

On the other hand, if your system prompts that WORK_DATE is a VARCHAR data type, then it means that WORK_DATE is not of numeric type and the following line will fail:

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

You can either enclose the data 20221104 by parentheses, or change to put this field as another parameter in the prepared statement.

So suggested change will be :

Change the block

$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

[Additional point]

If you want to automatically use "today's date", then

change

$work_date = '20221104';

$work_date=date('Ymd');

Alternatively, it is also good to use the following code (provided by Thang ??ai):

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

Source