How to fill a form input text box based on the value of another text box using Ajax

Question

I'm learning php, html and ajax. I've built a MySQL database with employee information. I've managed to figure out how to populate a text box (called Employee Details) automatically using ajax. When you start typing an employee's name, it will populate with a concatenation of their full name and company name.

What I'm trying to do now is to fill the second text box with their employee ID automatically based on the value of the first text box.

I've searched lots of questions and tutorials but I can't find a simple explanation of how to do this and the examples I have found don't include the php, ajax, html altogether and I can't figure out how to piece it all together (I'm not exactly a coding genius and I can't get any of the examples to work). and I've been stuck on this now for hours now and loosing the will to live!

I'd really appreciate it if someone could help me out with a simple explanation with an example of the php, ajax and html in one place!

Here's my code so far.

form.php

<link rel="stylesheet"        href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>

<script>
$(function() {
    $( "#employeedetails" ).autocomplete({
        source: 'search.php'
    });
});
</script>

<div class="form-group">
<b class="text-primary">Employee details:</b>
<input type="text" class="form-control" value="" id="employeedetails" name="employeedetails" required>

<b class="text-primary">Id:</b>
<input type="text" name="employeeid" id="employeeid" placeholder="employeeid"/>
</div>

search.php

include 'dbconnect.php';

    //get search term
    $searchTerm = $_GET['term'];

    //get matched data from employee table
    $query = $conn->query("SELECT * 
                         FROM employees 
                         WHERE (firstname LIKE '%".$searchTerm."%')
                            OR (surname LIKE '%".$searchTerm."%')
                            OR (companyname LIKE '%".$searchTerm."%')
                         ORDER BY firstname ASC

                        ");
    while ($row = $query->fetch_assoc()) {
        $data[] = $row['firstname'] . " " . $row['surname'] . " - " .     

    }

    //return json data
    echo json_encode($data);
?>

Answer 1

Assuming your HTML Employee Detail textbox has id="employeeDetail" and your HTML Employee Id textbox has id="employeeId" you can use jQuery to listen to a selection of Employee Detail, then send that selection via Ajax, and use the Ajax response to update the value of your Employee Id textbox. This would involve the following jQuery and PHP code:

jQuery code:

    $(document).ready(function(){
        $(#employeeDetail).on("change", function(){ //use an appropriate event handler here
             $.ajax({
                 method: "POST",
                 url: "getEmployeeId.php",
                 data: {
                    employee_detail: $("#employeeDetail").val(),
                 },
                 success: function(response){
                    if (response == "FALSE") {
                        var message = "ERROR: something went wrong on the MYSQL side";
                        alert(message);
                    } else {
                        $("#employeeId").val(response);
                    }
                 },
                 error: function(jqXHR, textStatus, errorThrown){
                    var message: "ERROR: something went wrong with the AJAX call - " + textStatus + " - " + errorThrown;
                    alert(message);
                 }
              });
         });
    });

PHP code (getEmployeeId.php):

    //set $server, $user, $password and $database_name, then establish the connection:
    $conn = new mysqli($server, $user, $password, $database_name);
    if ($conn->connect_error) {
        exit("FALSE");
    }
    //get employee detail from POST (sent via AJAX):
    $employee_detail = $_POST["employee_detail"];
    //here, you should test whether employee_detail matches what you expect
    //here, split $employee_detail into $first_name, $last_name and $company_name
    //now you are ready to send the MYSQL query:
    $sql = 'SELECT employeeid FROM tablename WHERE firstname = "$first_name" AND surname = "$last_name" AND companyname = "$company_name"';
    //since you expect a single matching result, you can test for num_rows == 1:
    if ((! $result = $_conn->query($sql)) || ($result->num_rows !== 1)) {
        exit("FALSE");
    } else {
        while ($row = $result->fetch_assoc()) {
            $employee_id = $row['id'];
        }
    }
    //now send $employee_id back to the AJAX call:
    echo $employee_id;