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
username
andid
from theusers
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
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.
its working well on localhost bt nt working on server
What problem are you getting –
data is not auto populating on the text box elements
I think there is some database related issue.
For checking open getDetails.php file and replace $result = mysqli_query($con,$sql); in if($request == 1) condition with $result = mysqli_query($con,$sql) or die(mysqli_query($con)); to know the error.
Nop .. database is working fine..I get mum of rows in response …bt can’t get it on text box
i am working in codeignitor
this page makes me confuse… please convert this to codeignitor
$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;
}
HOW TO AUTOCOMPLETE DATA ON MULTIPLE FIELDS WITH JQUERY AND AJAX using asp.net c#
How would you code if you want to autocomplete Username and Name Separately wherein the PHP to get the data are different?
For this, separately define autocomplete on the field username and name and select the data.
How do i code for inserting data into mysql from such a table
If I put the js part in a separate file the getDetail.php must be in the same directory, or I just give the relative path to it in the js file?
Hi Mathias,
No there is no need to put getDetail.php in the same directory where your .js file is stored.
If the getDetail.php is in root directory then in $.ajax call ‘getDetails.php’.
If the getDetail.php is also in a directory e.g. includes/getDetail.php then in $.ajax call it ‘includes/getDetail.php’.
HI, I’m looking a similar example but using XML as source, do you have this example but getting it from a remote XML file?
Regards
can you send complete source code please..
Hi Chiranjeevi,
Download link is available on the tutorial from where you can download.
Hi, Yogesh.
I searched but did not find it. How to convert a data column into hyperlink?
I have greatly appreciated your work. Very useful.
Thanks,
Mauro A Chaves
Hi Mauro,
Can you explain me in more detail what you are trying to do by emailing me at makitweb@gmail.com.
Suddenly stopped working sir could you have a look at it thanks
thanks for a good tutorial but i have problem with send the data into database after filling the the fields can you help me out please (am still student learning php)
Hi Gerald,
I will try to send you an example today.
thanks am waiting sir
thanks a lot sir
You’re welcome.
am sorry, i have trying to insert insert query and function the problem is i do know how to arrange it in array hence, on submitting it separate row for each field inserted
How to Delete a row in that table sir?
Add a new button for delete in a row. When delete button gets clicked then select the parent row and remove it. Here, is the working example link.
Tx sir,but how to save that data?
Add a save button. You need to loop on the rows and make an Array of columns fields values then send the Array to AJAX request on save button click. In AJAX file read the Array and insert the record.
Hello sir i combinate this code with my project but autocomplete cannot actif ..
Why error “TypeError: $(…).autocomplete is not a function ” in the code “$(“#”+id).autocomplete({“? thank you be for
Superb!!!!!
I was looking for days from last 2 days….Finally i got it here with proper explanation…
Thanx a lot……………
You’re welcome.
Hi,
I am facing problem even after opting this method. Data is not auto-populating in the text fields. Can you help me please?
Are you viewing any error on the browser console?
Thank you, for nice working script. Does someone solved delete button in each row? Thanks for answer.
This is a very informative article you post and you describe also in graphically its very interesting when i read it i clearly found and take out of some my confusion thanks for sharing this article. keep share more article we love it.
Good job sir , it works. I just had to add this link http://code.jquery.com/jquery-latest.min.js.Thank you.
You’re welcome.
Thank you so much for this snippet. I have been working on building an invoice system for my inventory program and this was the missing component. I am so glad to have found this. many, many thanks
I’m glad you found it useful.
I have been working with the script and was able to point it to my data. I created a copy of my invoice table and changed the field names to match those in your script. Everything worked perfectly (I have 850 items in my dropdown but it worked very well). I did have to put in text substitutes for the fname and lname fields as my table had only five fields.
I did have a question about inserting the table data into a mysql table. I plan to have the user select the items and then enter a quantity. I will probably have a script calculate the libe total and then I want to insert the data from the html table into another MySQL table (invoiceDetail). Do you also have a tutorial for this process?
Sir I properly understood your code, but I am getting problem in linking different files. Means which post of code to be included in which part of file.
Please help me
Hi Yogesh Singh,
Its working well for me except records which utf8 characters.
Please show me how can to fix. Thank you!
(Yogesh Singh,
For example, fname: Lý ; lname: Đức Trí)
Hi, which file shall I put the code in section 5 jQuery ?
i have error with url: “getDetails.php”,
Uncaught SyntaxError: Invalid or unexpected token
in 5. jQuery.
the file does exist
do you know what can be the error?
It works well. Can you teach me how to add auto compute with auto complete table like that. Im a student. Great work dude
Would it be possible to enter the data immediately after a full match?
I have a table with barcodes and articlename and partnumber.
When I scan the barcode it finds the record but I need to manually click the result line returned from the query.
The barcode scanner is basically sending 22200000 + a CR (carriage return) so ideally the CR should perform the click/select of the result. I can disable the CR also if needed.
I am stuck how to do thisz
Many thanks
This is EXACTLY what I’ve been looking for. I used your previous demo to create a simple autocomplete function, but I found that when the user actually selected a value, I needed to use one of the values returned to execute a second query.
Keep up the good work!
Thank you Sir for your code ! God bless you !
I was in need to put 3 related autocomplete in 3 cells of a dataTables , using data after search in a database .
Your code was very helpful ! Until now, I couldn’t imagine that Ajax can be used in a cell table.
Thank you !
You’re welcome.
Hello! Can you tell me how the code will change if the ‘users’ table does not have an ‘id’ field and there is no primary key?
In that case replace value of autocomplete suggestion result in ajaxfile.php with the value you want – response[] = array(“value”=>$row[‘username’],”label”=>$row[‘fname’]);. Update SQL query according to the value in $request == 2 – “SELECT * FROM users WHERE username='”.$username.”‘ “
https://www.awesomescreenshot.com/video/14093509?key=9f578a1593f70b551fbcc099df40c28c
Data is not coming on selecting the value
Hi Suresh,
I hope your problem is resolved if it is still not resolved can you share your code at makitweb@gmail.com so I can check it?