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 its id, product details, etc.

In the demonstration, I am using jQuery UI to display suggestion list and fetch details using PHP from the MySQL database table when a value from the suggestion list is 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 new 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 a new getDetails.php file.

Perform action based on a request from the AJAX call.

$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 = $_POST['request']; // request

// Get username list
if($request == 1){
 $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 = $_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: "getDetails.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: 'getDetails.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

Enter an username in the textbox and select option from suggestion list. Click on Add more button to add a new row. Open in a new tab.


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 which get on AJAX successful callback.

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

Related Post

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *