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  the element. It is a better way to allow the user to easily search for data in existing records e.g. get student details by its id, product details, etc. In the demonstration, I am using jQuery UI to display suggestion and fetch details from the MySQL database table when a value from the suggestion list. I am using PHP to fetch records from the MySQL database when AJAX call is invoked.   Contents Table structure Configuration HTML PHP jQuery Demo 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, `name` varchar(80) 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. $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 to get selected user details and bind it on input element on successful callback. Add more On add more button click create a new row with input element and append it on table element. 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 text box and click on add more button to add a new row. 7. Conclusion Searched for the record in MySQL database table with AJAX when input value in the textbox. Autocomplete data on another 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.

Be First to Comment

Leave a Reply

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