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.