0

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);
?>
data101
  • 145
  • 1
  • 5
  • 13
  • Where is the ID coming from? The database.. ? And based on what? Their first name, surname..? – clearshot66 Mar 02 '17 at 20:11
  • @clearshot66, yes of course sorry it would have been helpful if I'd described the MySQL database/table. The Employee ID is coming from the database table. It is the primary key for each record. The fields in the database for each record are employeeid (the unique primary key), firstname, surname and companyname. I'm naively hoping that each combination of firstname, surname and companyname will be unique to each primary key. – data101 Mar 02 '17 at 20:18
  • So what you'll do is use Jquery to grab the value of the first textbox once the user is done typing (setInterval, if you google how to check that it'll be one of top 5 results I was using it the other day), send that value via ajax to an external php page (as post/get) that'll query your database using the first textbox's value you sent it , then return the result of the query (the employee id) to the second textbox – clearshot66 Mar 02 '17 at 20:33
  • this is what I used: http://stackoverflow.com/questions/4220126/run-javascript-function-when-user-finishes-typing-instead-of-on-key-up – clearshot66 Mar 02 '17 at 20:34

1 Answers1

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;
ATJ
  • 309
  • 2
  • 10
  • Hi ATJ thanks for your answer, this is exactly what I was hoping for to have the PHP and Ajax in one place and especially for including my variable names in your answer! For some reason though I can't seem to get this to work still. I've amended the code with my own database values etc but the second text box is still empty once I've made the change to the first text box. Do you know if there's anything I could be missing? – data101 Mar 07 '17 at 12:36
  • @data101 I'm glad it helped. There are a few things that could be going wrong. As a starting-point, have you checked if `$("#employeeDetail").val()` and `response` contain what you expect? – ATJ Mar 07 '17 at 14:02
  • no I haven't. How do I do that? I noticed in your example it's doing clever things like displaying messages depending on success. Where do the messages appear? They don't display on my html page when I run the code. I'm sure I'm doing something stupid here! – data101 Mar 07 '17 at 14:57
  • @data101 One way to display error messages is to use `alert(message)`, which opens a little pop-up window displaying `message`.[Note I'd forgotten one `alert()` call inside the `success` call; you may want to add that one first, to check if you were getting any errors on the MySQL side.] To test whether `$("#employeeDetail").val()` is what you expect, use `alert($("#employeeDetail").val());` immediately after `$(document).ready(function(){` (i.e. before the AJAX call). To check if `response` contains what you expect, use `alert(response);` immediately after `var message`in the `success` call. – ATJ Mar 07 '17 at 15:04
  • @data101 Also, if you are using Chrome, another way to get error messages is to use the Chrome *Developer Tools* (which you access from the *View* menu > Developed > Developer Tools). This will display Javascript error messages in the *Console* tab. You can intentionally send messages to the console using `console.log(message)`. – ATJ Mar 07 '17 at 15:08
  • @data101 Another source of problems might be the choice of event handler. If you aren't sure what event handler to use, I suggest the following set-up, just for testing the Ajax call: add an HTML button (e.g. ``), then use the following event handler: `$(#testAjax).on("click",function(){etc.});` (instead of `$(#employeeDetail).on("change", function(){etc.});`). To test the Ajax call, just type something in your `#employeeDetail` textbox, then click the `#testAjax` button - that should launch the Ajax. – ATJ Mar 07 '17 at 22:45
  • really sorry for the radio silence... I've had a crazy week! I stripped everything back down to basics in my code and then I was able to implement your solution. Thanks for all your great tips, I really found your technique of using alerts to debug my code useful! I've accepted your answer and given you a +1... thanks again! – data101 Mar 15 '17 at 15:12