Autocomplete Textbox using jQuery, PHP and MySQL

---

---

The Autocomplete textbox allows the user to quickly find and select a value from the pre-populated option list. It displays the suggestions automatically while the user types into the field. Using the jQuery UI Autocomplete plugin, you can easily add an autocomplete textbox on the website. The jQuery UI Autocomplete plugin converts input field into an Autocomplete. When the user typing in the autocomplete input field, this plugin starts searching for matched values and list them to choose from.

Autocomplete textbox provides a user-friendly way to add a search input field with auto-suggestions dropdown in the web application. It is very useful when you want to populate suggestions from the database when the user starts typing in the input field. In this tutorial, we will show you how to implement Autocomplete textbox and display suggestions from the database using jQuery, PHP, and MySQL.

The example code will implement auto-suggestions input field for skills search. The autosuggestion skills will be fetched from the database and listed under the textbox while the user starts typing in the textbox.

Create Database Table

To store the autosuggestions data a table needs to be created in the database. The following SQL creates a skills table with some basic fields in the MySQL database.

CREATE TABLE `skills` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `skill` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Autocomplete Search Input Field

The following code attaches a autocomplete suggestion box to the input field with the jQuery UI plugin.

jQuery UI Autocomplete Plugin:
Include the jQuery and jQuery UI library files to use the Autocomplete plugin.

<!-- jQuery library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

<!-- jQuery UI library -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

Initialize Autocomplete:
The autocomplete() function initialize the Autocomplete plugin.

• Specify the selector ID/class (#skill_input) of the input field element where autocomplete feature will be attached.
• Specify the local or remote source (search.php) to retrieve the data for auto-suggestions.

<script>
$(function() {
    $("#skill_input").autocomplete({
        source: "search.php",
    });
});
</script>

HTML Input Element:
Initially, an input text field is provided to enter the skills.

• The ID of the input field must be specified in the autocomplete() method.
• The suggestions are fetched from the database and listed under the input field while starts typing in the textbox.
• The selected value will be set to the input text field for the further form submission process.

<label>Skills:</label>
<input type="text" id="skill_input"/>

Fetch Autocomplete Data from Database with PHP and MySQL (search.php)

The search.php file is called by the autocomplete() method of Autocomplete plugin. This file retrieves the skills data based on the search term and returns data as a JSON encoded array using PHP and MySQL.

The autocomplete() method make a GET request to source URL and added a query string with a term field. So, you can get the search term using PHP GET method. The following PHP code, fetch the records from the MySQL database (skills table) and filter the records by $_GET['term']. The filtered skill data returned to autocomplete() method as a JSON encoded array.

<?php 
// Database configuration 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "codexworld"; 

// Create database connection 
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 

// Check connection 
if ($db->connect_error) { 
    die("Connection failed: " . $db->connect_error); 
} 

// Get search term 
$searchTerm = $_GET['term']; 

// Fetch matched data from the database 
$query = $db->query("SELECT * FROM skills WHERE skill LIKE '%".$searchTerm."%' AND status = 1 ORDER BY skill ASC"); 

// Generate array with skills data 
$skillData = array(); 
if($query->num_rows > 0){ 
    while($row = $query->fetch_assoc()){ 
        $data['id'] = $row['id']; 
        $data['value'] = $row['skill']; 
        array_push($skillData, $data); 
    } 
} 

// Return results as json encoded array 
echo json_encode($skillData); 
?>

Replace Input Value with ID

On form submission, the selected item value is sent to the server-side for autocomplete input field. If you want to get the ID of the selected value, you need to replace the input field value with item ID.

$(function() {
    $("#skill_input").autocomplete({
        source: "search.php",
        select: function( event, ui ) {
            event.preventDefault();
            $("#skill_input").val(ui.item.id);
        }
    });
});

Autocomplete Widget Options

Various configuration options are available to customize the jQuery UI Autocomplete plugin functionality. Some useful configuration options of the Autocomplete plugin are given below.

• source – Required. The source must be specified from where the data will be fetched for suggestions list. You can specify the local or remote source.
  • Local source: An array can be used for local data. [ "Choice1", "Choice2" ] OR [ { label: "Choice1", value: "value1" }, { label: "Choice2", value: "value2" }, ... ]

    $( ".selector" ).autocomplete({
        source: [ "PHP", "Python", "Ruby", "JavaScript", "MySQL", "Oracle" ]
    });
    

  • Remote source: The URL can be used for a remote source that will return JSON data. http://example.com

    $( ".selector" ).autocomplete({
        source: "http://example.com"
    });
    

• minLength – Optional (Default 1). The minimum number of characters that must type before the search is performed.

  $( ".selector" ).autocomplete({
      minLength: 5
  });
  

• select( event, ui ) – Triggered when a value is selected from the suggestions.

  $( ".selector" ).autocomplete({
      select: function( event, ui ) {}
  });
  

Retrieve Autocomplete Input Value with PHP

Once the form is submitted, you can get the value of the autocomplete input field using the $_POST method in PHP. The following example code snippet shows how to submit the form and get the autocomplete field’s value using PHP.

1. HTML Form with Autocomplete Input:

<form method="post">
    <label>Your Skills:</label>
    <input type="text" name="skill_input" id="skill_input"/>
    <input type="submit" name="submit" value="SUBMIT"/>
</form>

2. Get Value of Autocomplete Input:
After the form submission, use the $_POST method in PHP to retrieve the value from the autocomplete input field.

if(isset($_POST['submit'])){ 
    $skill = $_POST['skill_input']; 
    echo 'Skill Input: '.$skill; 
}

jQuery UI Autocomplete with Images and Custom HTML in PHP

Are you want to get implementation help, or modify or enhance the functionality of this script? Submit Paid Service Request

If you have any questions about this script, submit it to our QA community - Ask Question