Get data automatically using SYSDATE?-CodePudding

I'm making a page to get data automatically by date using SYSDATE with Oracle base.WORK_DATE is formatted as VARCHAR2(20221115 ) And I got some errors like below:

SQLSTATE[HY000]: General error: 1821 OCIStmtExecute: ORA-01821: date format not recognized (ext\pdo_oci\oci_statement.c:157)

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

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

I tried a few ways but it didn't work. Please help me. Thanks

Code is:

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

$sql = "SELECT * 
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE =TO_CHAR(SYSDATE, '%Y%M%D') "; // TO_DATE(WORK_DATE , '%Y%M%D') = TRUNC(SYSDATE)
                                               // TO_CHAR(SYSDATE, '%Y%M%D') = WORK_DATE

    try{
      $params = [];
      if(isset($_GET['group']) && $_GET['group']){
        $sql = $sql. 'AND 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  >
      <div >
        <div >

          <div  style="background-color:LightGray;">
             <h2 >STATION DETAIL</h2>
          </div>

          <form method="get">

            <input type="text" name="groupname"  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  type="submit" name="filter" id="filter" style="">
                <i ></i> Filter
              </button>

          </form>          
          
          <br/><br/>

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

CodePudding user response：

The SQL is python formatted but formatted for the database. So

SELECT * 
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE =TO_CHAR(SYSDATE, '%Y%M%D')

should be

SELECT * 
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE =TO_CHAR(SYSDATE, 'YYYYMMDD')

and of course, standard recommendation applies here - try to store dates in the database as the DATE datatype, not varchar2