我正在制作一個頁面以使用 SYSDATE 和 Oracle base 按日期自動獲取資料。WORK_DATE 的格式為 VARCHAR2(20221115) 我得到了如下錯誤：
SQLSTATE[HY000]: 一般錯誤: 1821 OCIStmtExecute: ORA-01821: 無法識別日期格式 (ext\pdo_oci\oci_statement.c:157)
注意：未定義的變數：結果在第 88 行的 C:\xampp\htdocs\test_Oracle\src\index.php
警告：第 88 行為 C:\xampp\htdocs\test_Oracle\src\index.php 中的 foreach() 提供的引數無效
我嘗試了幾種方法，但沒有用。請幫我。謝謝
代碼是：
<?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 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>
uj5u.com熱心網友回復：
SQL 是 python 格式的，但需要針對資料庫進行格式化。所以
SELECT *
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE =TO_CHAR(SYSDATE, '%Y%M%D')
應該
SELECT *
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE =TO_CHAR(SYSDATE, 'YYYYMMDD')
當然，標準建議適用于此 - 嘗試將資料庫中的日期存盤為 DATE 資料型別，而不是 varchar2
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/534406.html
標籤：PHP甲骨文
上一篇：甲骨文資料庫|轉義整個字串變數
下一篇：Oracle變長子串函式