Autocomplete Textbox with Multiple Values using jQuery, PHP and MySQL

In this tutorial, you’ll learn how to implement autocomplete textbox with multiple values selection in PHP. We’ll use jQuery UI Autocomplete to show the suggestions from the MySQL database and users would be able to select multiple values.

Along with the autocomplete functionality, we’ll show how you can restrict the limitation on multiple values selection and get multiple values from multiple sources.

jQuery UI & jQuery Library

At first, you should need to include the jQuery UI stylesheet, jQuery library, and jQuery UI library.

<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>

jQuery UI Autocomplete with Multiple Values

In this example, we’ll display a textbox for skills entry. Once the user starts writing skills, the autosuggestion skills would be listed under the textbox. These skills auto suggestion would be fetched from the skills table of MySQL database in skills.php file and returns Json data to source option.

JavaScript:
The selector (#skills) of autocomplete function should match with the textbox ID and respective data source file should provide into source option.

<script>
$(function() {
    function split( val ) {
        return val.split( /,\s*/ );
    }
    function extractLast( term ) {
        return split( term ).pop();
    }
    
    $( "#skills" ).bind( "keydown", function( event ) {
        if ( event.keyCode === $.ui.keyCode.TAB &&
            $( this ).autocomplete( "instance" ).menu.active ) {
            event.preventDefault();
        }
    })
    .autocomplete({
        minLength: 1,
        source: function( request, response ) {
            // delegate back to autocomplete, but extract the last term
            $.getJSON("skills.php", { term : extractLast( request.term )},response);
        },
        focus: function() {
            // prevent value inserted on focus
            return false;
        },
        select: function( event, ui ) {
            var terms = split( this.value );
            // remove the current input
            terms.pop();
            // add the selected item
            terms.push( ui.item.value );
            // add placeholder to get the comma-and-space at the end
            terms.push( "" );
            this.value = terms.join( ", " );
            return false;
        }
    });
});
</script>

HTML:
Textbox ID should be matched with the selector of autocomplete function.

<label for="skills">Tag your skills: </label>
<input id="skills" size="50">

jQuery UI Autocomplete Limit on Multiple Values

The functionality of this example is same as the previous example, we only add one extra functionality on it. We’ll add a restriction on multiple selections. If the user exceeds the limitation of selection, extra values would be removed from the textbox.

For adding limitation, place the following JavaScript in select event. Here we have added the limit of 3, you can change it as per your need.

select: function( event, ui ) {
    var terms = split( this.value );
    if(terms.length <= 3) {
        // remove the current input
        terms.pop();
        // add the selected item
        terms.push( ui.item.value );
        // add placeholder to get the comma-and-space at the end
        terms.push( "" );
        this.value = terms.join( ", " );
        return false;
    }else{
        var last = terms.pop();
        $(this).val(this.value.substr(0, this.value.length - last.length - 2)); // removes text from input
        $(this).effect("highlight", {}, 1000);
        $(this).attr("style","border: solid 1px red;");
        return false;
    }
}

jQuery UI Autocomplete with Multiple Values from Different Sources

This example shows how to provide autocomplete from the different source file. Degrees auto-suggestion will be displayed at the time of first value selection and skills auto-suggestion will be displayed from the second values selection.

Place the following JavaScript in source option. Here you can see first autocomplete is getting from degrees.phpsource and second autocomplete is getting from skills.php source.

source: function( request, response ) {
    var terms = split( request.term );
    if (terms.length < 2) {
      $.getJSON("degrees.php", { term : extractLast( request.term )},response);
    }else{
       $.getJSON("skills.php", { term : extractLast( request.term )},response);
    }
}

Source Files

We can get the textbox value by term field ($_GET['term']) from the query string. We will fetch the data from skills or degrees table and filtering the skills or degrees by $_GET['term']. Filtered data would be returned as JSON format.

skills.php:

<?php
    //database configuration
    $dbHost = 'localhost';
    $dbUsername = 'root';
    $dbPassword = '';
    $dbName = 'codexworld';
    
    //connect with the database
    $db = new mysqli($dbHost,$dbUsername,$dbPassword,$dbName);
    
    //get search term
    $searchTerm = $_GET['term'];
    
    //get matched data from skills table
    $query = $db->query("SELECT * FROM skills WHERE name LIKE '%".$searchTerm."%' ORDER BY name ASC");
    while ($row = $query->fetch_assoc()) {
        $data[] = $row['name'];
    }
    
    //return json data
    echo json_encode($data);?>

degrees.php:

<?php
    //database configuration
    $dbHost = 'localhost';
    $dbUsername = 'root';
    $dbPassword = '';
    $dbName = 'codexworld';
    
    //connect with the database
    $db = new mysqli($dbHost,$dbUsername,$dbPassword,$dbName);
    
    //get search term
    $searchTerm = $_GET['term'];
    
    //get matched data from skills table
    $query = $db->query("SELECT * FROM degrees WHERE name LIKE '%".$searchTerm."%' ORDER BY name ASC");
    while ($row = $query->fetch_assoc()) {
        $data[] = $row['name'];
    }
    
    //return json data
    echo json_encode($data);?>

Conclusion

We’ve covered most required jQuery autocomplete functionalities. Using these functionalities, you can extend jQuery UI autocomplete and implement advanced autocomplete in your web project. If you know any other advanced functionality of jQuery UI autocomplete, you can share with us by commenting here.