Select2 is a jQuery plugin that extends the functionality of a simple HTML drop-down element by allowing to search the list, add the image with options, navigate to the option with arrow keys, etc.
It comes with AJAX support which you can call it in the same way as $.ajax in jQuery. This allows you to fetch options from the server when the user clicks on the select element or searches for a value in the search box.
In this tutorial, I show how you load MySQL database data remotely in Select2 with AJAX.
Contents
- Table structure
- Database Configuration
- Download and Include
- HTML
- PHP – Return select2 data
- jQuery – Initialize select2
- Demo
- Conclusion
1. Table structure
I am using users table in the example. It has following structure –
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(80) NOT NULL, `name` varchar(80) NOT NULL, `password` varchar(80) NOT NULL );
2. Database Configuration
Create a config.php for database configuration.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. Download and Include
- Download the library from here.
- Include
select2.min.cssandselect2.min.jsfiles with the jQuery library in the<head>section. You can also use the CDN.
<link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
<!-- Script -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
4. HTML
Create a single dropdown element. This is use to initialize select2 using jQuery.
Completed Code
<select id='selUser' style='width: 200px;'>
<option value='0'>- Search user -</option>
</select>
5. PHP – Return select2 data
Create a getData.php file. Using this file handle select2 AJAX requests.
Select 5 records from the users table if $_POST['searchTerm'] is not set otherwise select 5 records where $search matches in the name field.
NOTE – Remove LIMIT if you want to fetch all records or update its value.
Create $data Array that initializes with user id and name. Return the array in JSON format.
NOTE – While creating associative array for initialize the Array make sure that their you have defined
idandtextkeys otherwise data will not get loaded in the select2 element.
Completed Code
<?php
include 'config.php';
if(!isset($_POST['searchTerm'])){
$fetchData = mysqli_query($con,"select * from users order by name limit 5");
}else{
$search = $_POST['searchTerm'];
$fetchData = mysqli_query($con,"select * from users where name like '%".$search."%' limit 5");
}
$data = array();
while ($row = mysqli_fetch_array($fetchData)) {
$data[] = array("id"=>$row['id'], "text"=>$row['name']);
}
echo json_encode($data);
6. jQuery – Initialize select2
Call select2() method on the <select id='selUser'> to initialize the plugin. To load records remotely specify ajax option where set its url, type, dataType, delay, data, and processResults.
Get the typed search value using params.term in data. The successful callback handle by processResults function where initialize results with the response.
Completed Code
$(document).ready(function(){
$("#selUser").select2({
ajax: {
url: "getData.php",
type: "post",
dataType: 'json',
delay: 250,
data: function (params) {
return {
searchTerm: params.term // search term
};
},
processResults: function (response) {
return {
results: response
};
},
cache: true
}
});
});
7. Demo
8. Conclusion
Loading data in Select2 with AJAX is a powerful feature that allows you to create a more dynamic and data-driven search box.
In this example, I only passed the search value as data but you can also pass additional parameters with data that you require on the AJAX end.
If data is not loading in select2 then check your SQL queries again and try to debug it using the browser console and network tab.
View the official documentation for more details.
You can view this tutorial to know how to load data using PDO in select2.
If you found this tutorial helpful then don't forget to share.
well done!
it is amazing and it is the best select2 tutorial I ever seen before
Sir,can I predefined the selected option?
i am appreciated greatly your help
I solved it by the follow code
it require id and full_name
can I complete it by only id
many thanks
var option = new Option(data.full_name, data.id, true, true);
studentSelect.append(option).trigger(‘change’);
Awesome solution
if the select have an item selected, the json parameter’s selected:true,how working on the jquery?
very good tutorial, i needed this functionality for a select2 dropdown in yii2, i have googled it and found so many irrelevant answers, this one is the best i have seen ever
how select column with links, show as text not link
and thank very much for this tutorial
how select column with links, show as text not link, and open after select – https://www.soleis.info/select2/
Thanks for the aowesome lesson.
But what do I need to modify if I wanted to make 3 or select2 inputs with ajax in the same page.
I tried it with two, but the JS code looks not working any more?
Hi, Thanks for th tutorial.
how can I define the return value to be the name instead of the id.
Thanks
i want to select partiqular value on some situation. how can i do this?
means how to select perticular value
sir how I will show my user data as per it will only show the id of user then can u just give an idea so it will help me
Thank you
thank you very! much..
this had my day.
hi sir i need to show the data in to database mysql with select2 filter multiple checkbox with diffrent fiter field like country industry and emp range and sort the respected data plz help me
hi guys how do use this if i have two drop down list or more in one page
can you do the same solution with python as backend instead of php
Hi,
THIS IS URGENT…
This code is superb. Ajax call loading data on database query but not loading in the dropdown box in my case… Need help…
My CODE:
Part1:
Student Id *
– Search student –
Part2:
$(document).ready(function(){
$(“#StudentId”).select2({
ajax: {
url: “ajax_call_to_fetch_student_id.php”,
type: “post”,
//dataType: ‘html’,
delay: 250,
data: function (params) {
//alert(JSON.stringify(params));
return {
searchTerm: params.term // search term
};
},
processResults: function (response) {
alert(JSON.stringify(response)); //I can see here that records are coming
return {
results: response
};
},
cache: true
}
});
});
Thanks in Advance…
num_rows > 0){
$response = array();
$i = 0;
while($row = $result->fetch_assoc()){
$response[] = array(“id”=>$i++, “text”=>$row[‘Student_Id’]);
}
//print_r($response);
}
echo json_encode($response);
?>
Muito bom.
Muito útil.
Obrigado…
i tried your code, and working like a charm to show and seach data, but on search proccess i had a problem, if i search data, i can not search name in some id, here is my data:
id: 1, text: text1,
id:2, text: text2,
id: 3, text: text3,
id: 4, text: text4
if i search text1 or text3, data show text1 or text3, but if i search text2 or text4 it not show text2 or text4, i dont know what i mistake. i just copy and paste your code, with my database. can you help me
Thank you! I didn’t need the PHP part, just the JavaScript and this resolved the issues I was having. Thank you.
Hello,
I am getting issue in select2 ajax,
when i select from searched list of ajax another ajax request executed and due to that i my select 2 dropdown not close.