Autocomplete Textbox with jQuery UI using 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 the input field into an Autocomplete. When the user typing in the autocomplete input field, this plugin starts searching for matched values and lists 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 an auto-suggestions input field for skills search. The autosuggestion options 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,
  `name` 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.6.0/jquery.min.js"></script>

<!-- jQuery UI library -->
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.13.2/themes/smoothness/jquery-ui.css">
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.13.2/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 (fetchData.php) to retrieve the data for auto-suggestions.

<script>
$(function() {
    $("#skill_input").autocomplete({
        source: "fetchData.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.

<div class="form-group">
    <label>Programming Language:</label>
    <input type="text" id="skill_input" placeholder="Start typing...">
</div>

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

The fetchData.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 makes a GET request to the 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 name LIKE '%".$searchTerm."%' AND status = 1 ORDER BY name 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['name']; 
        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: "fetchData.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 ) {}
  });
  

Get Value from Autocomplete Input 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" action="submit.php">
    <!-- Autocomplete input field -->
    <div class="form-group">
        <label>Programming Language:</label>
        <input type="text" id="skill_input" name="skill_input" placeholder="Start typing...">
    </div>

    <!-- Submit button -->
    <input type="submit" name="submit" value="Submit">
</form>

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

<?php 
 
if(isset($_POST['submit'])){ 
    $skill = $_POST['skill_input']; 
    echo 'Selected Skill: '.$skill; 
} 
 
?>

jQuery UI Autocomplete with Images and Custom HTML in PHP

Conclusion

jQuery UI autocomplete input field is very useful for the search field where you want to list the term suggestions to help the user to enter the relative search keywords/terms. The jQuery UI Autocomplete plugin makes it easier to convert an HTML input field to autocomplete input. This example script allows the user to find relevant terms and select values from the pre-populated list for search/filter. You can use the selected value or ID from the autocomplete options list for server-side processing.