CODEWITHSUNDEEP
javascriptphpmysql
Glory
Glory

Reputation: 45

filtering database using select

I'm trying to filter my database using 2 select option() and () what I want is when the user load the page they can see database result organized in a table, when they choose the the select (drop down list) the table is filtered, they can choose one of the select (drop down list) or both for optimum result. so how can I adjust my code to work as I want it to ? I'm using oracle hr database but I keep getting this error: Undefined index , can some one tell me why?

index.php

<html>
<head>
</head>
<body>
<form  action="emp.php" method="post" name="Form2" id="Form2">
<select  id="officecode" name="officecode">
  <option value="">Select an officeCode:</option>
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
  <option value="4">4</option>
</select>
<select id="reportsTo" name="reportsTo" >
  <option value="">reports To:</option>
  <option value="1143">1143</option>
  <option value="1102">1102</option>
  <option value="1108">1108</option>
  <option value="1056">1056</option>
</select>
</form>
<br>
<div id="result"><b>
<?php include "emp.php"; ?>
</b></div>
<script>
$(document).ready(function(){
    var office = $("#officecode");
    var report = $("#reportsTo");
    the_office.change(function(){
        var the_selected_office = $(this).val();
        self.location = "emp.php?off="+the_selected_office+"&rep=";
    });
    the_report.change(function(){
        var the_selected_report = $(this).val();
        self.location = "emp.php?off=&rep="+the_selected_report+";
    });
});
</script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
</body>
</html>

emp.php

<?php
$office = $_POST["off"];
$report = $_POST["rep"];
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "classicmodels";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql="SELECT * FROM employees WHERE reportsTo= '".$office."' AND reportsTo= '".$report."' ";
$result = $conn->query($sql);

echo "<table>
<tr>
  <th>Firstname</th>
  <th>Lastname</th>
  <th>Employee Number</th>
  <th>Extension</th>
</tr>";
while($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row['firstName'] . "</td>";
    echo "<td>" . $row['lastName'] . "</td>";
    echo "<td>" . $row['employeeNumber'] . "</td>";
    echo "<td>" . $row['extension'] . "</td>";
    echo "</tr>";
}
?>

Upvotes: 1

Views: 747

Answers (1)

Maxi Schvindt
Maxi Schvindt

Reputation: 1462

Try this code: Two points, i comment database connect and the sql(for my) require join to other table, because your data or concept is wrong. In this case, the POST es empty, not same POST and GET, test code and see.

index.php

<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script>

$(document).ready(function(){

    var office = $("#officecode");
    var report = $("#reportsTo");

    $("#officecode").change(function(){
        var the_selected_office = $(this).val();
        self.location = "index.php?off="+the_selected_office+"&rep=";
    });

    $("#reportsTo").change(function(){
        var the_selected_report = $(this).val();
        self.location = "index.php?off=&rep="+the_selected_report+"";
    });


});

</script>

</head>
<body>
<select  id="officecode" name="officecode">
  <option value="">Select an officeCode:</option>
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
  <option value="4">4</option>
</select>

<select id="reportsTo" name="reportsTo" >
  <option value="">reports To:</option>
  <option value="1143">1143</option>
  <option value="1102">1102</option>
  <option value="1108">1108</option>
  <option value="1056">1056</option>
</select>
<br>
<div id="result"><b>
<?php include "emp.php"; ?>
</b></div>
</body>
</html>

emp.php

<?php

  print_r("<pre>");
  print_r($_GET);
  print_r($_POST);
  print_r("</pre>");

  /*if(isset($_GET["off"]) || isset($_GET["rep"])) {*/

    $office = $_GET["off"];
    $report = $_GET["rep"];

    $where = array();
    if(!empty($office)) { 
        $where[] = "officeCode = {$office}";
    } 

    if(!empty($report)) { 
        $where[] = "reportsTo = {$report}";
    } 


$servername = "localhost";
$username = "root";
$password = "";
$dbname = "classicmodels";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 



if($where) {
    $w = implode(" OR ", $where);   
} else {
    $w = 1;
}

$sql="SELECT * FROM employees WHERE {$w}";
print_r("<pre>");
print_r($sql);
print_r("</pre>");
$result = $conn->query($sql);

echo "<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Employee Number</th>
<th>Extension</th>

</tr>";
while($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row['firstName'] . "</td>";
    echo "<td>" . $row['lastName'] . "</td>";
    echo "<td>" . $row['employeeNumber'] . "</td>";
    echo "<td>" . $row['extension'] . "</td>";

    echo "</tr>";
}

/*}*/
?>

Empty filter OfficeCode = 3

Upvotes: 1

Related Questions