v

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.

Add instant site search to your website with autocomplete

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);
 
 });
});