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.
Contents
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
usernameandidfrom theuserstable and return an array.
- $request == 2 – Fetch user detail on the basis of POST
usernamevalue 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
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.