Hi, this post will show you to Create Autocomplete Textbox from Database in PHP, jQuery and MySQL. Autocomplete search is a feature that suggests the list of available options for selection based on the characters keyed in by the user. By implementing this autocomplete search feature to a text box in web forms, we can save the user from typing the complete set of words and let them pick out their choice by entering few characters instead.
Autocomplete Textbox in PHP using jQueryUI:
To implement autocomplete feature to textbox, we need jQueryUI Plugin.
Also Read:- How to Use Google reCAPTCHA in PHP Form
- How to Lazy Load Images in PHP and JavaScript
- How to Upload and Watermark Images in PHP
First download jquery ui plugin and extract its files to your working folder. Make sure you have both jquery ui's "css" and "js" files in place. The jquery ui plugin requires jquery library for it to work. So download jquery file and move it to the same working folder. Having everything in place, now let's move on to integrate autocomplete feature in php.
Auto Complete Textbox Example:
Here goes the process. I have a MySQL Database, "employee" with a table named "department". This department table stores the list of department names. Now I have a html form which requires the user to input department names. Here is what I want to bring in the autocomplete feature. That I want to pull off the entire department names from the database and to use it as a suggestion list for the department name input field. Let us see how to do this in php and jquery.
Implementing Autocomplete Textbox from Database in PHP
Create a file with name "autocomplete.php" to fetch all the department names from the database and convert and echo them as a json string (list of department names separated by commas). Here goes the php code to fetch the data from database for autocompletion.
autocomplete.php
<?php
$connection = mysqli_connect("localhost","username","password","employee") or die("Error " . mysqli_error($connection));
//fetch department names from the department table
$sql = "select department_name from department";
$result = mysqli_query($connection, $sql) or die("Error " . mysqli_error($connection));
$dname_list = array();
while($row = mysqli_fetch_array($result))
{
$dname_list[] = $row['department_name'];
}
echo json_encode($dname_list);
?>
The php json_encode() function will convert the array values to json string of comma (,) separated values.
Next create another file "demo.php". This is where we will have the textbox and use the "autocomplete.php" file to integrate auto complete feature.
Step-1) Load CSS & JS
First include the required css and js files inside the <head></head> section.
<!-- load jquery ui css--> <link href="path/to/jquery-ui.min.css" rel="stylesheet" type="text/css" /> <!-- load jquery library --> <script src="path/to/jquery-1.10.2.js"></script> <!-- load jquery ui js file --> <script src="path/to/jquery-ui.min.js"></script>
Step-2) Add Textbox Input
Next add the input textbox field within <body></body> section.
<label>Department Name</label></br> <input id="department_name" type="text" size="50" />
Step-3) JavaScript Function
Finally use jquery ui's autocomplete() method to add list of department names to the textbox as suggestions. Here is the js script for doing it.
<script type="text/javascript">
$(function() {
var availableTags = <?php include('autocomplete.php'); ?>;
$("#department_name").autocomplete({
source: availableTags,
autoFocus:true
});
});
</script>
Now run the file and type in the textbox. As soon as you type in the textbox you can see suggestions popping out below the textbox field like this.
 |
| PHP - jQuery - Autocomplete Textbox Example |
Here goes the complete code for the above implementation.
demo.php
<!DOCTYPE html>
<html>
<head>
<title>Autocomplete Textbox Demo | PHP | jQuery</title>
<!-- load jquery ui css-->
<link href="path/to/jquery-ui.min.css" rel="stylesheet" type="text/css" />
<!-- load jquery library -->
<script src="path/to/jquery-1.10.2.js"></script>
<!-- load jquery ui js file -->
<script src="path/to/jquery-ui.min.js"></script>
<script type="text/javascript">
$(function() {
var availableTags = <?php include('autocomplete.php'); ?>;
$("#department_name").autocomplete({
source: availableTags,
autoFocus:true
});
});
</script>
</head>
<body>
<label>Department Name</label></br>
<input id="department_name" type="text" size="50" />
</body>
</html>
And that explains implementing autocomplete textbox from database using php and jquery. You can easily implement this feature to web forms by using this method.
Also Read:- How to Create Simple Web Crawler in PHP
- How to Convert MySQL Data to JSON Format using PHP
- Dynamic Image Gallery in PHP, jQuery & Bootstrap
I hope you have enjoyed this autocomplete textbox in php tutorial. If you don't like using jquery library for auto suggest feature, there's an option called Datalist in HTML5. With this you can easily implement autocomplete textbox in php. You can check here for the tutorial.
Last Modified: Nov-10-2017
Wow thank you for your great article it works great....
ReplyDeletemovie tickets booking,
watch movies online.
Great tutorial! Thank you!
ReplyDeleteGlad you liked it :-)
Deleteam getting response from ajax. but its not loding into dropdown
ReplyDeleteam getting response from ajax. but its not loding into dropdown
ReplyDeleteam getting response from ajax. but its not loding into dropdown
ReplyDeleteHi, either you don't get the proper ajax response or the dropdown element id should be wrong.
DeleteJust debug and see if the json string is what you are expecting.
Cheers.
I want to create a autocomplete in which i can fill multiple options like, i have autocomplete box name countries i have visited so can we do that and all these countries should be displayed from database
ReplyDeleteHi! You can use a select box or option group with multiple options (like countries visited) and when the user selects one of the options, make ajax call to populate the autocomplete textbox with results based upon your queries.
DeleteI hope this helps :-)
Hi Valli - I think I'm close to getting this to work. I've followed your instruction and edited where necessary for my db structure and file paths, but do not get the suggested list yet. There's a link on the page: "fetch the data from database". I'm not sure how to use that since it seems the data is already pulled in and stored in an array ($dname_list) with the autocomplete.php file. Am I misunderstanding that link?
ReplyDeleteHi William, regarding the link you have mentioned, it's a different article. To autocomplete textbox you only have to follow the code given in this article.
DeleteCheers.
Thanks. I found the error - works now!
ReplyDeleteGlad you got it fixed! Cheers.
DeleteIf I want to list the members of a team, how would I modify this so I could could select 4 diferent names using the auto-suggest function?
ReplyDeleteHi, I want to autocomplete for two field in database like "Select * from department where department name Like '%name%' AND floor = '$floor' but it still doesn't work.
ReplyDeleteDo you know why ? thank you
Thanks Valli Pandy..,
ReplyDeleteIts really helpful..
I'm glad you like it.
DeleteCheers.
DISCULPA ME GENERA EL SIGUIENTE ERROR NO SE SI ME PUEDEN APOYAR
ReplyDeleteUncaught TypeError: Cannot read property 'label' of null
how to add links as results?
ReplyDeletehow to add links in dropdown
ReplyDeleteam not getting this in bootstarp modal
ReplyDeletenice post..
ReplyDeletesee more at:
Ghayal Once Again (2016) Hindi Movie Review
Glad you liked it! Cheers.
Deletesee more at:
ReplyDeleteEetti (2015) Tamil Movie Review
Good job very nice tutorials thanks a lot
ReplyDeleteThanks for your support:)
DeleteI need to pass a couple of variables to autocomplete.php for the SQL query ... how should I handle that ?
ReplyDeleteI need to pass $fn , $ln , $region , $practice
Thoughts how I do this ? These already are present in the page where my form is.
Thanks !
autocomplete() method has an option 'extraParams' to pass parameters. Try using it!
DeleteextraParams: { // pass params to autocomplete.php
"pass_params": "yes"
}
Hope this helps!
hello,
ReplyDeletehow to get both department name and department_id from database as per the program above.
You can try concatenating dept id and name as a single value and populate the textbox.
Delete...
$sql = $sql = "select dept_id, department_name from department";
...
while($row = mysqli_fetch_array($result))
{
$dname_list[] = $row['dept_id'] . " - " . $row['department_name'];
}
echo json_encode($dname_list);
Hope this helps!
Cheers.
Hi Valli,
Deletethanks for your post, it works perfectly. Just one thing, I noted that to autocomplete you must start typing exactly the db data, it i not searching for similar strings to compare what you type and what options are on db. Is is complicate to implement? The script is usefull if you know perfectly how to start typing but when you have for example 35 potential padidates on db field is not easy to remember.
Thanks for your comments.
Hi! What do you mean by saying you have to type the exact data from db? As soon as you start typing, it pops out suggestions containing the similar string or even a char for that matter. Say you type 's', then you will get a list of suggestions that contains the char 's' and so on. This method is good and fast if you have to work with small subset of data.
DeleteFor larger datasets, you have to use ajax calls to pull off data from db. This is the method search engines like google use. You can check this tutorial if you want to try it.
http://www.kodingmadesimple.com/2015/12/php-search-engine-script-for-mysql-database-ajax.html
I hope that clarifies your doubt.
Cheers.
Hi Valli, sorry, you are absoletly right, it was my mistake trying to modify the code, :(.
DeleteMaybe you can help me on this issue, I used the code to show in a form the text of one option, I mean, I have a tasks table and other task to define status, so in the form I use this code to show the task status description and not the ID. So easier for the user. But I have a basic proble I do not know how to solve. If I select the text privided by the script, them the db is not full with data caming from this field because is expected to receive 1, 2, 3 or 4 and status ID and not one text.
Any recomendation from your side? I´m a little bit lost, learning PHP and others by my side, :). Thanks in advance.
It it is working on my Firefox broweser but not on my IE. Is there anything that can make it work on all browsers? Thanks
ReplyDeleteThanks :)
ReplyDeleteThanks by tutorial.
ReplyDeleteI just implement it on my page: http://paginas-amarillas.avisus.net
I modified the CSS to highlight the search term.
How did you do this please?
Deleteidk what is happening but this $(function() {
ReplyDeletevar availableTags = ;
$("#itemName").autocomplete({
source: availableTags,
autoFocus:true
});
});
prevents my others js file from functioning.. why is that so? any idea? thanks.
Hi,
ReplyDeleteFantastic bit of code!
Is it possible to highlight the search term in the results?
Thanks.
Thanks for this code.
ReplyDeleteIs it possible to highlight the search term?
This is great, Thanks
ReplyDeleteTo display on modal you should add appendTo
$(document).ready(function() {
var autocom = ;
$("#notes").autocomplete({
source: autocom,
autoFocus:true,
appendTo: $("#auto_div")
});
});
Thank you very much,
ReplyDeleteYou have saved my day.
Welcome:)
Deletethanks for this
ReplyDeleteSeem to be getting the error: "Expected a ':' following the property name 'var'."
ReplyDeleteAny suggestions?
Seem to be getting the error: Expected a ':' following the property name 'var'.
ReplyDeleteAny suggestions?
thank you :)
ReplyDeletehi admin ajax is not working on my website any help https://www.subkuchsell.com
ReplyDeletei followed your method by the autocomplete display all the data from data base
ReplyDelete