How to autocomplete data on multiple fields with jQuery and AJAX

You can use jQuery AJAX to autocomplete data on the single or multiple elements when the user search or select value from an element.

It is a better way to allow the users to easily search for data in existing records and get required information e.g. get student details by their id, product details, etc.

In the demonstration, I am using jQuery UI to display a suggestion list and fetch details using PHP from the MySQL database table when a value from the suggestion list gets selected.

How to autocomplete data on multiple fields with jQuery and AJAX


Contents

  1. Table structure
  2. Configuration
  3. HTML
  4. PHP
  5. jQuery
  6. Demo
  7. Conclusion

1. Table structure

I am using users table in the example.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `fname` varchar(60) NOT NULL,
  `lname` varchar(60) NOT NULL,
  `email` varchar(80) NOT NULL,
  `age` int(2) NOT NULL,
  `salary` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a config.php to define database connection.

Completed Code

<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
   die("Connection failed: " . mysqli_connect_error());
}

3. HTML

Create a <table> layout with input elements. Attach jQuery UI autocomplete on the first textbox with jQuery. Also created an Add more button to add a new row when it gets clicked.

Completed Code

<div class="container">
 
  <table border='1' style='border-collapse: collapse;'>
    <thead>
      <tr>
        <th>Username</th>
        <th>Name</th>
        <th>Age</th>
        <th>Email</th>
        <th>Salary</th>
      </tr>
    </thead>
    <tbody>
      <tr class='tr_input'>
         <td><input type='text' class='username' id='username_1' placeholder='Enter username'></td>
         <td><input type='text' class='name' id='name_1' ></td>
         <td><input type='text' class='age' id='age_1' ></td>
         <td><input type='text' class='email' id='email_1' ></td>
         <td><input type='text' class='salary' id='salary_1' ></td>
      </tr>
    </tbody>
  </table>

  <br>
  <input type='button' value='Add more' id='addmore'>
</div>

4. PHP

Create ajaxfile.php file to handle AJAX requests.

Here, handle 2 requests –

  • $request == 1 – If POST request value is 1 then fetch username and id from the users table and return an array.
  • $request == 2 – Fetch user detail on the basis of POST username value and return an array.

Completed Code

<?php
include "config.php";

$request = 0;
if(isset($_POST['request'])){
   $request = $_POST['request'];
}
// Get username list
if($request == 1){
   $search = "";
   if(isset($_POST['search'])){
      $search = $_POST['search'];
   }

   $query = "SELECT * FROM users WHERE username like'%".$search."%'";
   $result = mysqli_query($con,$query);
 
   while($row = mysqli_fetch_array($result) ){
      $response[] = array("value"=>$row['id'],"label"=>$row['username']);
   }

   // encoding array to json format
   echo json_encode($response);
   exit;
}

// Get details
if($request == 2){
   
   $userid = 0;
   if(isset($_POST['userid'])){
      $userid = $_POST['userid'];
   }
   $sql = "SELECT * FROM users WHERE id=".$userid;

   $result = mysqli_query($con,$sql); 

   $users_arr = array();

   while( $row = mysqli_fetch_array($result) ){
      $userid = $row['id'];
      $fullname = $row['fname']." ".$row['lname'];
      $email = $row['email'];
      $age = $row['age'];
      $salary = $row['salary'];

      $users_arr[] = array(
          "id" => $userid, 
          "name" => $fullname,
          "email" => $email, 
          "age" =>$age, 
          "salary" =>$salary
      );
   }

   // encoding array to json format
   echo json_encode($users_arr);
   exit;
}

5. jQuery

Bind data

Initialize autocomplete on username textbox when keydown event triggers. Define source and select options in the autocomplete() method.

Set the source with the AJAX response according to value.

When an option is selected from the suggestion list then send an AJAX request to get user details and bind it on input element on successful callback.

Add more

When Add more button is gets clicked then create a new row with input elements and append it to the <table>.

Completed Code

$(document).ready(function(){

  $(document).on('keydown', '.username', function() {
 
     var id = this.id;
     var splitid = id.split('_');
     var index = splitid[1];

     // Initialize jQuery UI autocomplete
     $( '#'+id ).autocomplete({
        source: function( request, response ) {
           $.ajax({
              url: "ajaxfile.php",
              type: 'post',
              dataType: "json",
              data: {
                 search: request.term,request:1
              },
              success: function( data ) {
                 response( data );
              }
           });
        },
        select: function (event, ui) {
           $(this).val(ui.item.label); // display the selected text
           var userid = ui.item.value; // selected value

           // AJAX
           $.ajax({
              url: 'ajaxfile.php',
              type: 'post',
              data: {userid:userid,request:2},
              dataType: 'json',
              success:function(response){
 
                 var len = response.length;

                 if(len > 0){
                    var id = response[0]['id'];
                    var name = response[0]['name'];
                    var email = response[0]['email'];
                    var age = response[0]['age'];
                    var salary = response[0]['salary'];

                    // Set value to textboxes
                    document.getElementById('name_'+index).value = name;
                    document.getElementById('age_'+index).value = age;
                    document.getElementById('email_'+index).value = email;
                    document.getElementById('salary_'+index).value = salary;
 
                 }
 
              }
           });

           return false;
        }
     });
  });
 
  // Add more
  $('#addmore').click(function(){

     // Get last id 
     var lastname_id = $('.tr_input input[type=text]:nth-child(1)').last().attr('id');
     var split_id = lastname_id.split('_');

     // New index
     var index = Number(split_id[1]) + 1;

     // Create row with input elements
     var html = "<tr class='tr_input'><td><input type='text' class='username' id='username_"+index+"' placeholder='Enter username'></td><td><input type='text' class='name' id='name_"+index+"' ></td><td><input type='text' class='age' id='age_"+index+"' ></td><td><input type='text' class='email' id='email_"+index+"' ></td><td><input type='text' class='salary' id='salary_"+index+"' ></td></tr>";

     // Append data
     $('tbody').append(html);
 
  });
});

6. Demo

View Demo


7. Conclusion

Searched for the record in MySQL database table with AJAX according to the input value in the textbox. Autocomplete data on multiple input elements with information that get on AJAX successful callback.

You can also check my earlier tutorial about auto-populate the dropdown with jQuery AJAX.

If you found this tutorial helpful then don't forget to share.

55 thoughts on “How to autocomplete data on multiple fields with jQuery and AJAX”

  1. i am working in codeignitor
    this page makes me confuse… please convert this to codeignitor
    $row[‘id’],”label”=>$row[‘username’]);
    }

    // encoding array to json format
    echo json_encode($response);
    exit;
    }

    // Get details
    if($request == 2){
    $userid = $_POST[‘userid’];
    $sql = “SELECT * FROM users WHERE id=”.$userid;

    $result = mysqli_query($con,$sql);

    $users_arr = array();

    while( $row = mysqli_fetch_array($result) ){
    $userid = $row[‘id’];
    $fullname = $row[‘fname’].” “.$row[‘lname’];
    $email = $row[’email’];
    $age = $row[‘age’];
    $salary = $row[‘salary’];

    $users_arr[] = array(“id” => $userid, “name” => $fullname,”email” => $email, “age” =>$age, “salary” =>$salary);
    }

    // encoding array to json format
    echo json_encode($users_arr);
    exit;
    }

    Reply
  2. If I put the js part in a separate file the getDetail.php must be in the same directory, or I just give the relative path to it in the js file?

    Reply
    • Hi Mathias,
      No there is no need to put getDetail.php in the same directory where your .js file is stored.

      If the getDetail.php is in root directory then in $.ajax call ‘getDetails.php’.

      If the getDetail.php is also in a directory e.g. includes/getDetail.php then in $.ajax call it ‘includes/getDetail.php’.

      Reply
  3. HI, I’m looking a similar example but using XML as source, do you have this example but getting it from a remote XML file?

    Regards

    Reply
  4. Hi, Yogesh.
    I searched but did not find it. How to convert a data column into hyperlink?
    I have greatly appreciated your work. Very useful.
    Thanks,

    Mauro A Chaves

    Reply
  5. thanks for a good tutorial but i have problem with send the data into database after filling the the fields can you help me out please (am still student learning php)

    Reply
  6. am sorry, i have trying to insert insert query and function the problem is i do know how to arrange it in array hence, on submitting it separate row for each field inserted

    Reply
  7. Superb!!!!!
    I was looking for days from last 2 days….Finally i got it here with proper explanation…
    Thanx a lot……………

    Reply
  8. Hi,

    I am facing problem even after opting this method. Data is not auto-populating in the text fields. Can you help me please?

    Reply
  9. This is a very informative article you post and you describe also in graphically its very interesting when i read it i clearly found and take out of some my confusion thanks for sharing this article. keep share more article we love it.

    Reply
  10. Thank you so much for this snippet. I have been working on building an invoice system for my inventory program and this was the missing component. I am so glad to have found this. many, many thanks

    Reply
  11. I have been working with the script and was able to point it to my data. I created a copy of my invoice table and changed the field names to match those in your script. Everything worked perfectly (I have 850 items in my dropdown but it worked very well). I did have to put in text substitutes for the fname and lname fields as my table had only five fields.
    I did have a question about inserting the table data into a mysql table. I plan to have the user select the items and then enter a quantity. I will probably have a script calculate the libe total and then I want to insert the data from the html table into another MySQL table (invoiceDetail). Do you also have a tutorial for this process?

    Reply
  12. Sir I properly understood your code, but I am getting problem in linking different files. Means which post of code to be included in which part of file.
    Please help me

    Reply
  13. Hi Yogesh Singh,
    Its working well for me except records which utf8 characters.
    Please show me how can to fix. Thank you!

    Reply
  14. i have error with url: “getDetails.php”,
    Uncaught SyntaxError: Invalid or unexpected token
    in 5. jQuery.
    the file does exist
    do you know what can be the error?

    Reply
  15. Would it be possible to enter the data immediately after a full match?
    I have a table with barcodes and articlename and partnumber.
    When I scan the barcode it finds the record but I need to manually click the result line returned from the query.
    The barcode scanner is basically sending 22200000 + a CR (carriage return) so ideally the CR should perform the click/select of the result. I can disable the CR also if needed.
    I am stuck how to do thisz
    Many thanks

    Reply
  16. This is EXACTLY what I’ve been looking for. I used your previous demo to create a simple autocomplete function, but I found that when the user actually selected a value, I needed to use one of the values returned to execute a second query.
    Keep up the good work!

    Reply
  17. Thank you Sir for your code ! God bless you !

    I was in need to put 3 related autocomplete in 3 cells of a dataTables , using data after search in a database .

    Your code was very helpful ! Until now, I couldn’t imagine that Ajax can be used in a cell table.

    Thank you !

    Reply
  18. Hello! Can you tell me how the code will change if the ‘users’ table does not have an ‘id’ field and there is no primary key?

    Reply
    • In that case replace value of autocomplete suggestion result in ajaxfile.php with the value you want – response[] = array(“value”=>$row[‘username’],”label”=>$row[‘fname’]);. Update SQL query according to the value in $request == 2 – “SELECT * FROM users WHERE username='”.$username.”‘ “

      Reply

Leave a Reply to Yogesh Singh Cancel reply