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 >
<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:
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';
to
$sql = $sql. ' AND GROUP_NAME=:GROUP_NAME';
On the other hand, if your system prompts that WORK_DATE is a VARCHAR2 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
to
// 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