# PHP MySQLi jQuery UI Autocomplete Database Search

## PHP MySQLi jQuery UI Autocomplete Database Search

June 17, 2022 by [Sanjay Kumar](https://onlinewebtutorblog.com/author/admin/)

Table of Contents

* [Create Database & Table](https://onlinewebtutorblog.com/php-mysqli-jquery-ui-autocomplete-database-search/#Create_Database_Table)
* [Dummy Data for Application](https://onlinewebtutorblog.com/php-mysqli-jquery-ui-autocomplete-database-search/#Dummy_Data_for_Application)
* [Application Folder Structure](https://onlinewebtutorblog.com/php-mysqli-jquery-ui-autocomplete-database-search/#Application_Folder_Structure)
* [Database Configuration](https://onlinewebtutorblog.com/php-mysqli-jquery-ui-autocomplete-database-search/#Database_Configuration)
* [Frontend Dropdown Layout](https://onlinewebtutorblog.com/php-mysqli-jquery-ui-autocomplete-database-search/#Frontend_Dropdown_Layout)
* [Ajax Handler & Functions](https://onlinewebtutorblog.com/php-mysqli-jquery-ui-autocomplete-database-search/#Ajax_Handler_Functions)
* [Application Testing](https://onlinewebtutorblog.com/php-mysqli-jquery-ui-autocomplete-database-search/#Application_Testing)

Share this Article

*
*
*
*
*

Reading Time: 7 minutes 54 Views

Inside this article we will see the concept of autocomplete search using jquery ui PHP MySQLi. This autocomplete search will be total dynamic which searches data from database.

You will get the complete concept of PHP MySQLi jQuery UI Autocomplete Database Search. Autocomplete is a jQuery UI plugin feature which gives the flexibility to search value from a list of values.

Article contains classified information about Autocomplete Database Search of jQuery UI in PHP MySQLi. It will help to create this advance feature.

Learn More –

* [How to Generate Fake Image URLs in PHP Using Faker](https://onlinewebtutorblog.com/how-to-generate-fake-image-urls-in-php-using-faker/)
* [How To Generate QR Code in PHP Using Library Tutorial](https://onlinewebtutorblog.com/how-to-generate-qr-code-in-php-using-library-tutorial/)
* [PHP MySQLi Dynamic Dependent Dropdown using jQuery Ajax](https://onlinewebtutorblog.com/php-mysqli-dynamic-dependent-dropdown-using-jquery-ajax/)
* [How To Use PHP MySQLi Transaction & Commit Statement](https://onlinewebtutorblog.com/how-to-use-php-mysqli-transaction-commit-statement/)

Let’s get started.

### Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

```
CREATE DATABASE php_applications;
```

Inside this database, we need to create a table.

Tables we need – **countries**.

```
CREATE TABLE `countries` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sortname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phonecode` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

### Dummy Data for Application

Here, you need to copy mysql query from this given file and run into **sql tab** of phpmyadmin. First download it and run.

[Download Countries Data](https://onlinewebtutorblog.com/wp-content/uploads/2022/04/countries.txt)

You will see something like this after this test data insertion.

![](https://onlinewebtutorblog.com/wp-content/uploads/2022/06/PHP-MySQLi-Tutorial-Load-Data-using-jQuery-AJAX-in-autocomplete.webp?ezimgfmt=rs:623x439/rscb5/ng:webp/ngcb5)

### Application Folder Structure

You need to create a folder structure to develop this application in PHP and MySQLi. Have a look the files and folders inside this application –

Create a folder with name **php-autocomplete** and create these **3 files** into it.

![](https://onlinewebtutorblog.com/wp-content/uploads/2022/06/php-mysqli-autocomplete-tutorial.png?ezimgfmt=rs:312x179/rscb5/ng:webp/ngcb5)

* ![](https://assets.vlitag.com/widget/2020/06/22/1592801729.jpg)![](https://assets.vlitag.com/widget/2019/11/05/1572962870.jpg)![](https://assets.vlitag.com/widget/2019/11/05/1572962830.jpg)![](https://assets.vlitag.com/widget/2020/07/30/1596163502.jpg)![](https://assets.vlitag.com/widget/2022/03/31/1648753746.png)![](https://assets.vlitag.com/widget/2022/03/31/1648753124.png)![](https://assets.vlitag.com/widget/2019/11/05/1572962830.jpg)![](https://assets.vlitag.com/widget/2020/06/22/1592801729.jpg)![](https://assets.vlitag.com/widget/2019/11/05/1572962870.jpg)

![](https://assets.vlitag.com/widget/2019/11/05/1572962830.jpg)

### Database Configuration

Open **dbconfig.php** file from folder. Add these lines of code into it.

dbconfig.php

```
<?php
```

```
/*
```

```
 @Author: Sanjay Kumar
```

```
 @Project: PHP & MySQLi Autocomplete Search From Database Using jQuery UI
```

```
 @Email: onlinewebtutorhub@gmail.com
```

```
 @Website: https://onlinewebtutorblog.com/
```

```
*/
```

```
​
```

```
// Database configuration
```

```
$host   = "localhost";
```

```
$dbuser = "admin";
```

```
$dbpass = "Admin@123";
```

```
$dbname = "php_applications";
```

```
​
```

```
// Create database connection
```

```
$conn = new mysqli($host, $dbuser, $dbpass, $dbname);
```

```
​
```

```
// Check connection
```

```
if ($conn->connect_error) {
```

```
    die("Connection failed: " . $conn->connect_error);
```

```
}
```

```
​
```

### Frontend Dropdown Layout

Open **index.php** file from application. Add these lines of code into it.

index.php

```
<?php
```

```
/*
```

```
 @Author: Sanjay Kumar
```

```
 @Project: PHP & MySQLi Autocomplete Search From Database Using jQuery UI
```

```
 @Email: onlinewebtutorhub@gmail.com
```

```
 @Website: https://onlinewebtutorblog.com/
```

```
*/
```

```
​
```

```
// Include the functions file
```

```
require 'functions.php';
```

```
?>
```

```
​
```

```
<!DOCTYPE html>
```

```
<html>
```

```
​
```

```
<head>
```

```
    <title>PHP & MySQLi Autocomplete Search From Database Using jQuery UI</title>
```

```
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
```

```
​
```

```
    <!-- jQuery UI CSS -->
```

```
    <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">
```

```
​
```

```
    <!-- jQuery -->
```

```
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
```

```
​
```

```
    <!-- jQuery UI JS -->
```

```
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
```

```
</head>
```

```
​
```

```
<body>
```

```
​
```

```
    <div class="container" style="margin-top: 40px;">
```

```
        <div class="panel panel-primary">
```

```
            <div class="panel-heading">PHP & MySQLi Autocomplete Search From Database Using jQuery UI</div>
```

```
            <div class="panel-body">
```

```
                <label>Search Country...</label>
```

```
                <input class="form-control" id="autocomplete_country" type="text">
```

```
                <br>
```

```
                <p>Country ID : <span id="countryId"></span></p>
```

```
            </div>
```

```
        </div>
```

```
    </div>
```

```
​
```

```
    <!-- Script -->
```

```
    <script type='text/javascript'>
```

```
        $(document).ready(function() {
```

```
            // Initialize
```

```
            $("#autocomplete_country").autocomplete({
```

```
                source: function(request, response) {
```

```
                    // Fetch data
```

```
                    $.ajax({
```

```
                        url: "functions.php",
```

```
                        type: "post",
```

```
                        dataType: "json",
```

```
                        data: {
```

```
                            search: request.term
```

```
                        },
```

```
                        success: function(data) {
```

```
                            response(data.data);
```

```
                        }
```

```
                    });
```

```
                },
```

```
                select: function(event, ui) {
```

```
                    // Set selection
```

```
                    $('#autocomplete_country').val(ui.item.label); // display the selected text
```

```
                    $('#countryId').text(ui.item.value); // save selected id to input
```

```
                    return false;
```

```
                },
```

```
                focus: function(event, ui) {
```

```
                    $("#autocomplete_country").val(ui.item.label);
```

```
                    $("#countryId").text(ui.item.value);
```

```
                    return false;
```

```
                },
```

```
            });
```

```
        });
```

```
    </script>
```

```
​
```

```
</body>
```

```
​
```

```
</html>
```

### Ajax Handler & Functions

Open **functions.php** file. Add these lines of it.

functions.php

```
<?php
```

```
/*
```

```
 @Author: Sanjay Kumar
```

```
 @Project: PHP & MySQLi Autocomplete Search From Database Using jQuery UI
```

```
 @Email: onlinewebtutorhub@gmail.com
```

```
 @Website: https://onlinewebtutorblog.com/
```

```
*/
```

```
​
```

```
// Include the database configuration file 
```

```
require 'dbconfig.php';
```

```
​
```

```
if ($_POST) {
```

```
    // Get data via ajax
```

```
    $search = isset($_POST['search']) && !empty($_POST['search']) ? $_POST['search'] : "";
```

```
​
```

```
    $itemRecords = array();
```

```
​
```

```
    if (!empty($search)) {
```

```
​
```

```
        $countrySelect = $conn->prepare("SELECT * FROM countries WHERE name LIKE CONCAT('%', ?, '%')");
```

```
        $countrySelect->bind_param("s", $search);
```

```
    } else {
```

```
​
```

```
        $countrySelect = $conn->prepare("SELECT * FROM countries");
```

```
    }
```

```
​
```

```
    $countrySelect->execute();
```

```
    $countries = $countrySelect->get_result();
```

```
​
```

```
    while ($item = $countries->fetch_assoc()) {
```

```
        extract($item);
```

```
        $itemDetails = array(
```

```
            "value" => $id,
```

```
            "label" => $name
```

```
        );
```

```
        array_push($itemRecords, $itemDetails);
```

```
    }
```

```
​
```

```
    echo json_encode(array(
```

```
        "status" => true,
```

```
        "data" => $itemRecords
```

```
    ));
```

```
}
```

```
​
```

### Application Testing

Now,

URL: <http://localhost/php-autocomplete/index.php>

![](https://onlinewebtutorblog.com/wp-content/uploads/2022/06/php-mysqli-autocomplete-jquery-ui-tutorial.png?ezimgfmt=rs:634x235/rscb5/ng:webp/ngcb5)

Download Complete Source Code

[Download Source Code](https://github.com/owthub/articles/tree/master/php-autocomplete)

We hope this article helped you to PHP MySQLi jQuery UI Autocomplete Database Search in a very detailed way.

[Buy Me a Coffee](https://www.buymeacoffee.com/onlinewebtutor)

Online Web Tutor invites you to try [**Skillshare**](https://www.skillshare.com/r/user/onlinewebtutor?gr_tch_ref=on) free for 1 month! Learn CakePHP 4, Laravel APIs Development, CodeIgniter 4, Node Js, etc into a depth level. Master the Coding Skills to Become an Expert in Web Development. So, Search your favourite course and enroll now. Click [**here**](https://www.skillshare.com/r/user/onlinewebtutor?gr_tch_ref=on) to join.

If you liked this article, then please subscribe to our [**YouTube Channel**](https://www.youtube.com/onlinewebtutor?sub_confirmation=1) for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on [**Twitter**](https://twitter.com/owthub) and [**Facebook**](https://www.facebook.com/owthub).