Select2 is a widely used jQuery plugin. It makes select element user-friendly.
After initializing it adds style and a search box. Based on the search text it filters the options.
It allows loading data dynamically using jQuery AJAX.
In this tutorial, I show how you can initialize select2 plugin and load data dynamically using AJAX PDO and PHP.
Contents
1. Table structure
I am using users table in the example –
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) NOT NULL, `name` varchar(80) NOT NULL, `email` varchar(80) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. Configuration
Create a config.php for a database connection.
Completed Code
<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "tutorial";
// Create connection
try{
$conn = new PDO("mysql:host=$server;dbname=$dbname","$username","$password");
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
die('Unable to connect with the database');
}
3. Download
- Download the Select2 library from GitHub and also download the jQuery library.
- Extract the downloaded files in the project directory.
- Also, copy the jQuery library.
4. HTML
Include select2.min.css, jQuery, and select2.min.js in the <head > section.
Create a <select id='selUser'> element.
Completed Code
<!doctype html>
<html>
<head>
<title>Dynamically load data in Select2 with AJAX PDO and PHP</title>
<meta charset="UTF-8">
<!-- jQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<!-- select2 css -->
<link href='select2/dist/css/select2.min.css' rel='stylesheet' type='text/css'>
<!-- select2 script -->
<script src='select2/dist/js/select2.min.js'></script>
<!-- CDN -->
<!-- <link href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.10/css/select2.min.css" rel="stylesheet" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.10/js/select2.min.js"></script>
-->
</head>
<body>
<select id='selUser' style='width: 200px;'>
<option value='0'>- Search user -</option>
</select>
</body>
</html>
5. jQuery
Initialize select2 on <select id='selUser' > element.
Use 'ajax' option to load data.
Set url: 'ajaxfile.php', type: 'post', dataType: 'json'.
With 'data' option passes the search text – searchTerm: params.term.
Handle AJAX response with 'processResults' option. Initialize results with the response.
Completed Code
$(document).ready(function(){
$("#selUser").select2({
ajax: {
url: "ajaxfile.php",
type: "post",
dataType: 'json',
delay: 250,
data: function (params) {
return {
searchTerm: params.term // search term
};
},
processResults: function (response) {
return {
results: response
};
},
cache: true
}
});
});
6. AJAX File
Create a new ajaxfile.php file.
Check if searchTerm is POST or not.
If not POST then fetch 10 records from the users table otherwise use searchTerm to search on the name field in the users table. I am fetching only 10 records. If you want to fetch all records then remove LIMIT from SQL or you can adjust the value of $numberofrecords.
Loop on the fetched records.
Initialize $response Array with id and text keys. Pass user id in id key and pass user name in text key.
Return $response Array in JSON format.
Completed Code
<?php
include 'config.php';
// Number of records fetch
$numberofrecords = 10;
if(!isset($_POST['searchTerm'])){
// Fetch records
$stmt = $conn->prepare("SELECT * FROM users ORDER BY name LIMIT :limit");
$stmt->bindValue(':limit', (int)$numberofrecords, PDO::PARAM_INT);
$stmt->execute();
$usersList = $stmt->fetchAll();
}else{
$search = $_POST['searchTerm'];// Search text
// Fetch records
$stmt = $conn->prepare("SELECT * FROM users WHERE name like :name ORDER BY name LIMIT :limit");
$stmt->bindValue(':name', '%'.$search.'%', PDO::PARAM_STR);
$stmt->bindValue(':limit', (int)$numberofrecords, PDO::PARAM_INT);
$stmt->execute();
$usersList = $stmt->fetchAll();
}
$response = array();
// Read Data
foreach($usersList as $user){
$response[] = array(
"id" => $user['id'],
"text" => $user['name']
);
}
echo json_encode($response);
exit();
7. Demo
8. Conclusion
Initialize select2 on the select element and use the 'ajax' option to load data. Return Array must contain id and text keys.
You can view this tutorial to know how to load data using MySQLi in select2.
If you found this tutorial helpful then don't forget to share.