Search using Select Dropdown in PHP (Oracle database)?-CodePudding

I'm making a website with a search function using select dropdown and am having a hard time. I want the web to be able to search for both model and group name at the same time or maybe just search for model/groupname.

I tried some ways but it didn't work.Because I'm new to programming, my knowledge is still poor, so I hope everyone can help me with my mistake or can keep me a code with similar content for reference. Thank you very much.

Code is:

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

$sql = "SELECT * 
FROM SFISM4.R_STATION_ATE_T
WHERE WORK_DATE= TO_CHAR(SYSDATE, 'YYYYMMDD') "; 
    try{
      $params = [];
      if(isset($_GET['model'], $_GET['group']) && $_GET['model'] && $_GET['group']){
        $sql = $sql. 'AND MODEL_NAME=:MODEL_NAME '.'AND GROUP_NAME=:GROUP_NAME';
        $params= [
          'MODEL_NAME' => $_GET['model'],
          '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 MODEL_NAME FROM SFISM4.R_STATION_ATE_T ');
      $stm->execute();
      $result_model = $stm->fetchAll(PDO::FETCH_OBJ);
    }catch(PDOException $e){
      echo $e->getMessage();
    }

    try{
        $stm = $db->prepare('SELECT DISTINCT GROUP_NAME FROM SFISM4.R_STATION_ATE_T');
        $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 Details</h2>
          </div>

          <form method="get">

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

            </select>

            <select name="group" id="group">
                <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>WORK_DATE</th>
                <th>PASS_QTY</th>
                <th>FAIL_QTY</th>
               
              </tr>
              <?php
                foreach($result as $key => $value):
              ?>
              <tr>
                <td> <?php echo $value['MODEL_NAME']?></td>
                <td> <?php echo $value['GROUP_NAME']; ?>
                <td> <?=$value['STATION_NAME']; ?>
                <td> <?=$value['WORK_DATE']; ?>
                <td> <?=$value['PASS_QTY']; ?>
                <td> <?=$value['FAIL_QTY'] ?>
                    
              </tr>
              <?php endforeach; ?>
            </thead>
          </table>

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

  </body>
</html>

CodePudding user response：

I don't speak PHP, but I presume that's irrelevant for what you asked:

I want the web to be able to search independently for 1 select or both at the same time.

If I understood you correctly, these "both searches" are to be done for model/or group name. If that's so, then modify query to look like this:

select * 
from sfism4.r_station_ate_t
where work_date= to_char(sysdate, 'YYYYMMDD')
  and (model_name = :model_name or :model_name is null)
  and (group_name = :group_name or :group_name is null)

CodePudding user response：

You may amend the code so that the $sql and $params will depend on whether there is/are actually $_GET['model'] and/or $_GET['group']

So change the block

$params = [];
      if(isset($_GET['model'], $_GET['group']) && $_GET['model'] && $_GET['group']){
        $sql = $sql. 'AND MODEL_NAME=:MODEL_NAME '.'AND GROUP_NAME=:GROUP_NAME';
        $params= [
          'MODEL_NAME' => $_GET['model'],
          'GROUP_NAME' => $_GET['group']
        ];
      }


$model=$_GET['model'];
$group=$_GET['group'];

  $params = [];

   if(isset($model) && $model!=""){   
     $sql = $sql. ' AND MODEL_NAME=:MODEL_NAME ' ;
     $params['MODEL_NAME']=$model;
    }

   if(isset($group) && $group!=""){   
     $sql = $sql. ' AND GROUP_NAME=:GROUP_NAME ' ;
     $params['GROUP_NAME']=$group;
    }

By using the code above,

if there is no $group nor $model, then the $sql will not contain these 2 conditions.
If only one of them exists, then only the corresponding condition will be added by "AND".
If both $group and $model exist, then both will be added to the condition.