How to Autocomplete Textbox from Database in PHP, jQuery and MySQL

On 2/19/2015

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.

Using jQueryUI to Autocomplete Textbox in PHP

To implement autocomplete feature to textbox, we need jQueryUI Plugin.

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 situation. 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: 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: 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: 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.

autocomplete-textbox-in-php-example
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:

I hope you have enjoyed this autocomplete textbox in php tutorial.

Last Modified: Jun-2-2016

45 comments:

  1. am getting response from ajax. but its not loding into dropdown

    ReplyDelete
  2. am getting response from ajax. but its not loding into dropdown

    ReplyDelete
  3. am getting response from ajax. but its not loding into dropdown

    ReplyDelete
    Replies
    1. Hi, either you don't get the proper ajax response or the dropdown element id should be wrong.

      Just debug and see if the json string is what you are expecting.

      Cheers.

      Delete
  4. 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

    ReplyDelete
    Replies
    1. Hi! 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.

      I hope this helps :-)

      Delete
  5. 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?

    ReplyDelete
    Replies
    1. Hi 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.

      Cheers.

      Delete
  6. Thanks. I found the error - works now!

    ReplyDelete
  7. If 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?

    ReplyDelete
  8. Hi, 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.

    Do you know why ? thank you

    ReplyDelete
  9. Thanks Valli Pandy..,
    Its really helpful..

    ReplyDelete
  10. DISCULPA ME GENERA EL SIGUIENTE ERROR NO SE SI ME PUEDEN APOYAR
    Uncaught TypeError: Cannot read property 'label' of null

    ReplyDelete
  11. how to add links as results?

    ReplyDelete
  12. am not getting this in bootstarp modal

    ReplyDelete
  13. Good job very nice tutorials thanks a lot

    ReplyDelete
  14. I need to pass a couple of variables to autocomplete.php for the SQL query ... how should I handle that ?

    I need to pass $fn , $ln , $region , $practice

    Thoughts how I do this ? These already are present in the page where my form is.

    Thanks !

    ReplyDelete
    Replies
    1. autocomplete() method has an option 'extraParams' to pass parameters. Try using it!

      extraParams: { // pass params to autocomplete.php
      "pass_params": "yes"
      }

      Hope this helps!

      Delete
  15. hello,
    how to get both department name and department_id from database as per the program above.

    ReplyDelete
    Replies
    1. You can try concatenating dept id and name as a single value and populate the textbox.

      ...

      $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.

      Delete
    2. Hi Valli,

      thanks 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.

      Delete
    3. 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.

      For 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.

      Delete
    4. Hi Valli, sorry, you are absoletly right, it was my mistake trying to modify the code, :(.

      Maybe 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.


      Delete
  16. 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

    ReplyDelete
  17. Thanks by tutorial.
    I just implement it on my page: http://paginas-amarillas.avisus.net
    I modified the CSS to highlight the search term.

    ReplyDelete
  18. idk what is happening but this $(function() {
    var availableTags = ;
    $("#itemName").autocomplete({
    source: availableTags,
    autoFocus:true
    });
    });
    prevents my others js file from functioning.. why is that so? any idea? thanks.

    ReplyDelete
  19. Hi,

    Fantastic bit of code!

    Is it possible to highlight the search term in the results?

    Thanks.

    ReplyDelete
  20. Thanks for this code.

    Is it possible to highlight the search term?

    ReplyDelete
  21. This is great, Thanks
    To display on modal you should add appendTo
    $(document).ready(function() {
    var autocom = ;
    $("#notes").autocomplete({
    source: autocom,
    autoFocus:true,
    appendTo: $("#auto_div")
    });
    });

    ReplyDelete
  22. Thank you very much,
    You have saved my day.

    ReplyDelete

Contact Form

Name

Email *

Message *