Filter my select options by department and location

Question

I have a problem in my website, I have 2 dropdown selects that are Departments and Locations, my problem is when I select a certain department and start typing a name of an employee it will work and bring back only employees from that department, but when I changed department to a different department it then brings back ALL employees rather than only employees with the letter in there name that are typed in the searchbar, I have been told I can do this with the KeyUp event and two Change events in javascript and an ajax call to my php routine. Could anyone help with this?

// Html Code //

 <div class="filter col col-12 col-sm-12 col-md-6 position-relative d-inline justify-content-center">
          <!------Filter Personnel by Departments-->

          <select id="departmentsFilter" class="border border-dark my-2 p-1 d-inline justify-content-center" placeholder="Departments">
            <option value="0" disabled selected>Filter By Department</option>
            <option value="1">Human Resources</option>
            <option value="2">Sales</option>
            <option value="3">Marketing</option>
            <option value="4">Legal</option>
            <option value="5">Services</option>
            <option value="6">Research and Development</option>
            <option value="7">Product Management</option>
            <option value="8">Training</option>
            <option value="9">Support</option>
            <option value="10">Engineering</option>
            <option value="11">Accounting</option>
            <option value="12">Business Development</option>
          </select>

          <!------Filter Personnel by Locations-->
          <select id="locationsFilter" class="border border-dark my-2             p-1 d-inline justify-content-center">
            <option value="0" disabled selected>Filter By         Location</option>
            <option value="1">London</option>
            <option value="2">New York</option>
            <option value="3">Paris</option>
            <option value="4">Munich</option>
            <option value="5">Rome</option>
          </select>
        </div>

        </div>

I was given this php file as a example of how I could implement what I need to do along with my javascript code

// php code //

$searchTerm = "%" . $_REQUEST["searchTerm"] . "%";

$sqlStr = "SELECT p.id, p.lastName, p.firstName, p.jobTitle, p.email, 
            d.name as department, l.name as location 
            FROM personnel p 
            LEFT JOIN department d ON (p.departmentID = d.id) 
            LEFT JOIN location l ON (d.locationID = l.id)
            WHERE (p.firstName LIKE '$searchTerm' OR p.lastName LIKE '$searchTerm')";


if (isset($_REQUEST["department"]) and strlen($_REQUEST["department"]) > 0) {

    $sqlStr = $sqlStr . " and d.id =" . $_REQUEST["department"];

}

if (isset($_REQUEST["location"]) and strlen($_REQUEST["location"]) > 0) {

    $sqlStr = $sqlStr . " and l.id =" . '"' . $_REQUEST["location"] . '"';

}

Answer 1

First of all you must download jquery which will be used to make api calls, use this link to download jquery: https://way2tutorial.com/jquery/jquery_download.php Once you have download the jquery file rename it to 'jquery.js'.

Suppose you create a javascript file name 'functions.js'

This file contains 2 functions:

function sendRequest(url,data){
    
    return $.ajax({
        url:url,
        type: "POST",
        data:data,
        cache: false,
        success: function(dataResult){
            
        },
        error:function(jqxhr, status, error){
            
        }
    });
    
}

    function onChangeDepartmentAndName(){
        var departmentName = document.getElementById("departments").value;
        var empName = document.getElementById("empNameInput").value;
    
        $.when(sendRequest('urltophpfile.php', 
        {dpName:departmentName,empName:empName})).done((data)=>{
            console.log(data);
            //the variable data will be an array of object, so create an html script from it and use innerHTML to embbed this script in an html element or a table
        }).fail((err)=>{
            console.log(err);
        })

    }

Now your html code will be like

      <script src="jquery.js" type="text/javascript"></script>
      <script src="functions.js" type="text/javascript"></script>

      <select id="departments" class="border border-dark my-2 p-1 d-inline 
      justify-content-center" placeholder="Departments" 
      onchange="onChangeDepartmentAndName()">
        <option disabled selected>Filter By Department</option>
        <option>Human Resources</option>
        <option>Sales</option>
        <option>Marketing</option>
        <option>Legal</option>
        <option>Services</option>
        <option>Research and Development</option>
        <option>Product Management</option>
        <option>Training</option>
        <option>Support</option>
        <option>Engineering</option>
        <option>Accounting</option>
        <option>Business Development</option>
      </select>

     <input type="text" id="empNameInput" keyup="onChangeDepartmentAndName()"/>

And you php code will be like this:

$departmentName = $_POST["dpName"];
$empName = $_POST["empName"];//this indexes should have the same name as the keys in the JSON Object in javascript code.
$con = mysqli_connect("localhost","root","password","dbname");
$getEmployeesQuery = "SELECT empName from employees where empName like '%".$empName."%' AND dpName = '".$departmentName."'";
$queryRes = mysqli_query($con,$getEmployeesQuery);
$empArray = array();
while($empRecord = mysqli_fetch_array($queryRes)){
$empArray[] = $empRecord;
}

mysqli_close($con);
print json_encode($empArray);

And that's it. But be carefull, making an api call on each keyup or change event is resource consuming and slow.