CODEWITHSUNDEEP
phporacle-database
Glory
Glory

Reputation: 45

Passing a select value to Oracle database

I'm working on this code I used Oracle HR database as my database, I'm trying to filter database results based on multiple select, but I keep getting this error:

Warning: oci_execute(): ORA-00933
Warning: oci_fetch_array(): ORA-24374

and no results, my code:

index.php

<html>
<head>
<title>Employees Search</title>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script type="text/javascript">
function checkinput()
{
    if(document.Form2.DEPARTMENT_ID.value=="" && document.Form2.MANAGER_ID.value=="")
    {
        alert("Please select department or manager!");
        return;
    }
    document.Form2.submit();
}
</script>

</head>
<body> 

<form method="get" name="Form2" id="Form2">
    <select  id="DEPARTMENT_ID" name="DEPARTMENT_ID">
        <option value="">DEPARTMENT:</option>
        <option value="90">90</option>
        <option value="60">60</option>
        <option value="100">100</option>
        <option value="30">30</option>
    </select>
    <select id="MANAGER_ID" name="MANAGER_ID" >
        <option value="">MANAGER ID:</option>
        <option value="100">100</option>
        <option value="102">102</option>
        <option value="103">103</option>
        <option value="108">108</option>
    </select>
    <input type="button" value="   Search   " onclick="checkinput()" />
</form>

<br />
<div id="result"> 
<?php include "emp.php"; ?>
</div>
</body>
</html>

emp.php

<?php

    $conn = oci_connect('hr', ' ', 'My-PC:1521/XE');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
    if(isset($_GET['DEPARTMENT_ID'])||isset($_GET['MANAGER_ID'])){
        $DEPARTMENT_ID=$_GET['DEPARTMENT_ID'];
        $MANAGER_ID=$_GET['MANAGER_ID'];
        $sql="SELECT * FROM EMPLOYEES WHERE ";

        if($DEPARTMENT_ID != "") 
        {
            $sql = $sql."DEPARTMENT_ID=".$DEPARTMENT_ID;
            if($MANAGER_ID != "") 
            {
                $sql = $sql."MANAGER_ID=".$MANAGER_ID;
            }
        }
        else
        {
            if($MANAGER_ID != "") 
            {
                $sql = $sql."MANAGER_ID=".$MANAGER_ID;
            }
        }

        $stid = oci_parse($conn, $sql);
        oci_execute($stid);

        echo " <table><tr><th>Employee No.</th><th>First Name</th><th>Last Name</th><th>PHONE NUMBER</th><th>Email</th><th>Job Title</th><th>MANAGER_ID</th><th>DEPARTMENT_ID</th></tr>";
        while ($row = oci_fetch_array($stid, OCI_ASSOC )) {
            echo "<tr>";
            echo "<td>".$row['EMPLOYEE_ID']."</td>";
            echo "<td>".$row['FIRST_NAME']."</td>";
            echo "<td>".$row['LAST_NAME']."</td>";
            echo "<td>".$row['PHONE_NUMBER']."</td>";
            echo "<td>".$row['EMAIL']."</td>";
            echo "<td>".$row['JOB_ID']."</td>";
            echo "<td>".$row['MANAGER_ID']."</td>";
            echo "<td>".$row['DEPARTMENT_ID']."</td>";
            echo "</tr>";
            echo"</table>";
        }
    }
?>

Upvotes: 0

Views: 655

Answers (1)

Nagendra Nigade
Nagendra Nigade

Reputation: 876

Everything seems correct expect SQL when Department and manager id is not null.

<?php

    $conn = oci_connect('hr', ' ', 'My-PC:1521/XE');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }
    if(isset($_GET['DEPARTMENT_ID'])||isset($_GET['MANAGER_ID'])){
        $DEPARTMENT_ID=$_GET['DEPARTMENT_ID'];
        $MANAGER_ID=$_GET['MANAGER_ID'];
        $sql="SELECT * FROM EMPLOYEES WHERE ";

        if($DEPARTMENT_ID != "") 
        {
            $sql = $sql."DEPARTMENT_ID=".$DEPARTMENT_ID;
            if($MANAGER_ID != "") 
            {
                $sql = $sql."AND MANAGER_ID=".$MANAGER_ID;
            }
        }
        else
        {
            if($MANAGER_ID != "") 
            {
                $sql = $sql."MANAGER_ID=".$MANAGER_ID;
            }
        }

        $stid = oci_parse($conn, $sql);
        oci_execute($stid);

        echo " <table><tr><th>Employee No.</th><th>First Name</th><th>Last Name</th><th>PHONE NUMBER</th><th>Email</th><th>Job Title</th><th>MANAGER_ID</th><th>DEPARTMENT_ID</th></tr>";
        while ($row = oci_fetch_array($stid, OCI_ASSOC )) {
            echo "<tr>";
            echo "<td>".$row['EMPLOYEE_ID']."</td>";
            echo "<td>".$row['FIRST_NAME']."</td>";
            echo "<td>".$row['LAST_NAME']."</td>";
            echo "<td>".$row['PHONE_NUMBER']."</td>";
            echo "<td>".$row['EMAIL']."</td>";
            echo "<td>".$row['JOB_ID']."</td>";
            echo "<td>".$row['MANAGER_ID']."</td>";
            echo "<td>".$row['DEPARTMENT_ID']."</td>";
            echo "</tr>";
            echo"</table>";
        }
    }
?>

Changed Line : $sql = $sql."AND MANAGER_ID=".$MANAGER_ID;

May be reason as SQL failed to parse this query.

As If both are not empty and assumed 10 then you query will be looked like SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=10 MANAGER_ID=10; which is not syntactically correct.

Upvotes: 1

Related Questions