Remote Data in Select2 Dropdown with Ajax using PHP

Select2 is a jQuery select box replacement plugin that allows customized select boxes with support for searching, tagging, and remote data loading features. You can convert HTML select boxes to highly featured dropdown select boxes using the Select2 JavaScript library. In this tutorial, we will show you how to integrate the Select2 dropdown with remote data loading using PHP.

The remote data loading feature is very useful when a huge number of options are required to list in a select box dropdown. Instead of listing all the options at once, it will be a great idea to load data remotely from the server side based on the choice of the user.

In this example script, we will create a select box with search and multi-select options with Ajax using PHP.

• Integrate the Select2 library to convert the HTML select box to the searchable select dropdown.
• Load data from the database remotely with Ajax using PHP.
• Allow multiple options select with tagging feature using Select2 jQuery.

Select2 Basic Usage

Select2 is designed to replace the standard <select> boxes in the advanced dropdown. In the following code snippet, we will show you a basic example of Select2 usage.

• Define an HTML select box element.

<select class="country-select" name="country">
  <option value="AU">Australia</option>
    ...
  <option value="US">United States</option>
</select>

Include the jQuery and Select2 library files.

<!-- Include jQuery library -->
<script src="js/jquery.min.js"></script>

<!-- Include Select2 jQuery library -->
<link rel="stylesheet" href="select2/css/select2.min.css">
<script src="select2/js/select2.min.js"></script>

Call the select2() method on an HTML selector to initialize the Select2 plugin.

<script>
$(document).ready(function() {
    $('.country-select').select2();
});    
</script>

Load Remote Data in Select2 Dropdown with Ajax using PHP

The following example shows how to fetch the select box options from the database remotely and listed in Select2 dropdown.

Create Database Table:
A table is required to store select box options data in the database. The following SQL creates a users table with some basic fields in the MySQL database.

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

jQuery and Select2 Library:
Include the jQuery and Select2 library files.

<!-- jQuery library -->
<script src="js/jquery.min.js"></script>

<!-- Select2 library files -->
<link rel="stylesheet" href="select2/css/select2.min.css">
<script src="select2/js/select2.min.js"></script>

HTML Select box Element:
Define a <select> element with HTML.

• Initially, a select box dropdown field will be displayed.
• On clicking it, the select box will expand with the search option.

<select name="user_id" id="user_select">
  <option value="">-- Select user --</option>
</select>

Select2 Remote Data with Ajax:
Use the select2() method and specify the selector (#user_select) to attach Select2 dropdown to the select box.

• Use the ajax option to load remote data from the server-side script (remote_data.php).
• Use the placeholder option to specify the placeholder text in the select box dropdown.
• The minimumInputLength option allows you to set the limit the search will start loading the remote data.

<script>
$(document).ready(function() {
    $("#user_select").select2({
        ajax: {
            url: "remote_data.php",
            dataType: 'json',
            data: function (params) {
                var query = {
                    search: params.term,
                    type: 'user_search'
                }

                // Query parameters will be ?search=[term]&type=user_search
                return query;
            },
            processResults: function (data) {
                return {
                    results: data
                };
            }
        },
        cache: true,
        placeholder: 'Search for a user...',
        minimumInputLength: 1
    });
});
</script>

Fetch Remote Data from Database with PHP and MySQL (remote_data.php):
The remote_data.php file is loaded by the select2 ajax method to fetch data from the database and list them as options in the select box dropdown.

• Get the search term using the $_GET method in PHP.
• Fetch the matched records from the database with PHP and MySQL.
• Return options data as JSON encoded array using PHP json_encode() function.

<?php 
 
// Database configuration  
$dbHost     = "localhost";  
$dbUsername = "root";  
$dbPassword = "root";  
$dbName     = "codexworld_db";  
  
// Create database connection  
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);  
  
// Check connection  
if ($db->connect_error) {  
    die("Connection failed: " . $db->connect_error);  
} 
 
if(!empty($_GET['type']) && $_GET['type'] == 'user_search'){ 
    $search_term = !empty($_GET['search'])?$_GET['search']:''; 
 
    // Fetch matched data from the database  
    $query = $db->query("SELECT * FROM users WHERE name LIKE '%".$search_term."%' AND status = 1 ORDER BY name ASC");  
     
    // Generate array with filtered records  
    $usersData = array();  
    if($query->num_rows > 0){  
        while($row = $query->fetch_assoc()){  
            $data['id'] = $row['id'];  
            $data['text'] = $row['name'];  
            array_push($usersData, $data);  
        }  
    }  
     
    // Return results as json encoded array  
    echo json_encode($usersData);  
} 
 
?>

Get Selected Option Value with PHP

Once the form is submitted, you can get the value of the selected option from the select box field using the $_POST method in PHP.
The following example code snippet shows how to submit the form and get the <select> field’s value using PHP.

HTML Form with Select2 Dropdown:

<form method="post">
  <select class="select2" name="user_id" id="user_select">
    <option value="">-- Select user --</option>
  </select>

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

Get Value of Select Box:
After the form submission, use the $_POST method in PHP to retrieve the value from the selected option.

<?php 
 
if(!empty($_POST['user_id'])){  
  $selected_user_id = $_POST['user_id']; 
 
  echo '<p>Selected User ID: '.$selected_user_id.'</p>';  
} 
 
?>

Select Multiple Values with Select2 Tagging

If you want to select multiple options, add a “multiple” attribute in the <select> tag.

• You can search from remote data and select multiple options.
• To submit multiple selected values, the <select> name attribute should be defined in the form of an array (user_id[]).

<select name="user_id[]" id="user_select" multiple="multiple">
  <option value="">-- Select users --</option>
</select>

Get Multiple Selected Option Values with PHP:
On form submission, you will get the selected values as an array using PHP $_POST method.

<?php 
 
if(!empty($_POST['user_id'])){  
  $selected_user_id_arr = $_POST['user_id']; 
 
  echo '<p>Selected User IDs: '.implode(',', $selected_user_id_arr).'</p>';  
} 
 
?>

Tags Input with Autocomplete using jQuery and PHP

Conclusion

Here we provide an easy way to convert a standard select box into a searchable dropdown with remote data support using Select2 and PHP. You can use this select box dropdown to list a large number of options set in an HTML <select> element. In this example code sample, we demonstrate the select box options list and search for users’ data. You can use Select2 for any type of data list dropdown, like country, state, city, etc.