Reading Time: 7 minutes

1,742 Views

Inside this article we will use the concept of PHP MySQLi Select2 Ajax Autocomplete Search Tutorial. Article contains classified information about loading data into select2 plugin via ajax request.

How to Load data using jQuery AJAX in Select2 using PHP MySQLi, we will see in a very clear way here.

Select2 is a jquery plugin which alters HTML element and create dynamic element with ajax data. Select2 also contains itself a search function into it. This tutorial will cover all easy steps to integrate and implement this functionality.

Learn More –

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.

Table 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

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

Application Folder Structure

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

Create a folder with name php-mysqli-selec2-search and create these 3 files into it.

Database Configuration

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

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi Select2 Ajax Autocomplete Search Tutorial
 @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);
}

Application Programming

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

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi Select2 Ajax Autocomplete Search Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/
?>
<html lang="en">

<head>
    <title>PHP MySQLi Select2 Ajax Autocomplete Search Tutorial</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.0/css/bootstrap.min.css" />
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.0/js/bootstrap.min.js"></script>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/css/select2.min.css" rel="stylesheet" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/js/select2.min.js"></script>
</head>

<body>
    <div class="row mt-5">
        <div class="col-md-6 offset-3 mt-5">
            <div class="card">
                <div class="card-header bg-primary text-center text-white">
                    <h6 class="m-0">PHP MySQLi Select2 Ajax Autocomplete Search Tutorial</h6>
                </div>
                <div class="card-body" style="height: 280px;">
                    <div>
                        <select class="countryList form-control" name="countryList"></select>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script type="text/javascript">
        $('.countryList').select2({
            placeholder: 'Select a country',
            ajax: {
                url: "data.php",
                dataType: 'json',
                delay: 250,
                data: function(data) {
                    return {
                        searchTerm: data.term // search term
                    };
                },
                processResults: function(response) {
                    return {
                        results: response
                    };
                },
                cache: true
            }
        });
    </script>

</body>

</html>

Ajax Handler – Load Data

Open data.php file from application. Write these lines of code into it.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP MySQLi Select2 Ajax Autocomplete Search Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @Website: https://onlinewebtutorblog.com/
*/

// Include the database configuration file 
require 'dbconfig.php';

// To read ajax
$countrySearchTerm = isset($_REQUEST['searchTerm']) && !empty($_REQUEST['searchTerm']) ? $_REQUEST['searchTerm'] : "";

if (!empty($countrySearchTerm)) {

    $countrySelect = $conn->prepare("SELECT * FROM countries WHERE name LIKE CONCAT('%', ?, '%')");
    $countrySelect->bind_param("s", $countrySearchTerm);
} else {

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

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

$itemRecords = array();
while ($item = $countries->fetch_assoc()) {
    extract($item);
    $itemDetails = array(
        "id" => $id,
        "text" => $name
    );
    array_push($itemRecords, $itemDetails);
}

echo json_encode($itemRecords);

die;

Application Testing

Now,

URL: http://localhost/php-mysqli-selec2-search/index.php

Load data in Select2 Ajax Search

Load data in Select2 Ajax – Search value

Download Complete Source Code

Download Source Code

We hope this article helped you to Learn PHP MySQLi Select2 Ajax Autocomplete Search Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.