Values are not getting matched while using php with oracle

So i have just started working on PHP with Oracle and I am getting values from user using select option as you can see in HTML file, however when i apply where condition on ‘Job’, it wouldn’t match the values. I have also used ‘upper’ with where condition to avoid case-sensitivity but it wouldn’t work. On the contrary when i apply where condition on ‘deptno’, it works fine. Is there anything I am missing?

<html>
    <body>
        
        
        <form action="emp_info.php" method="POST">
            <b>Job:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</b>                     
            <select name="Employee" id="Employee">
                <option value="CLERK">CLERK</option>
                <option value="SALESMAN">SALESMAN</option>
                <option value="MANAGER">MANAGER</option>
                <option value="ANALYST">ANALYST</option>
                <option value="PRESIDENT">PRESIDENT</option>
              </select>
              <br>
              <br>
              <b>Department Number:</b>                     
              <select name="Department" id="Department">
                  <option value="10">ten</option>
                  <option value="20">20</option>
                  <option value="30">30</option>
                </select>
              <br>
              <br>
                <input type="submit">
        </form>
    </body>
</html>

emp_info.php

<?php

   $db_sid = 
   "(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-KFR5A5D)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )";            // Your oracle SID, can be found in tnsnames.ora  ((oraclebase)appYour_usernameproduct11.2.0dbhome_1NETWORKADMIN) 
  
   $db_user = "scott";   // Oracle username e.g "scott"
   $db_pass = "tiger1234";    // Password for user e.g "1234"
   $deptno1=$_POST['Department'];
   $job1=$_POST['Employee'];
   echo $job1;
   $con = oci_connect($db_user,$db_pass,$db_sid); 
   if($con) 
      { echo "Oracle Connection Successful."; } 
   else 
      { die('Could not connect to Oracle: '); } 
      

     $q = "select empno,ename,job, mgr, to_char(hiredate,'dd/mm/yyyy') hiredate, sal, comm, deptno".
     " from emp".
     " where job=$job1";
     $query_id3 = oci_parse($con, $q);
     $runselect = oci_execute($query_id3); 
     

     while($row = oci_fetch_array($query_id3, OCI_BOTH+OCI_RETURN_NULLS)) 
     {
     
     echo "<br>".$row["JOB"]."<br>";
     $empno = $row["EMPNO"];
     $ename = $row["ENAME"];
     $job = $row["JOB"];
     $mgr = $row["MGR"];
     $hiredate = $row["HIREDATE"];
     $sal = $row["SAL"];
     $comm = $row["COMM"];
     $deptno = $row["DEPTNO"];

   } 

?>

Answer

You may need single quotes to be in the SQL string around the value. But before doing that, read the back half of The Underground PHP and Oracle Manual and do things like use bind variables (for security and performance) instead of doing " where job=$job1". Also check the examples on https://php.net/manual/en/ref.oci8.php (Finally, consider using a NOW doc for SQL).

Advertisement

Answer