How To Use Select2 Remote AJAX With Example in JQuery, PHP And MySQL

Many of us are still confused how to implement the select2 JQuery plugin using Remote AJAX. Here I will share how to implement it using Jquery, PHP, and MySql. In addition, to beautify the view, select2 plugin can also facilitate the user in selecting the data displayed on the element select if it has a lot of data. Follow the tutorial select2 using the following ajax to apply to your application.

Surely did you already know “What is select2 JQuery Plugin” ?

Select2 is a jquery plugin to replace the default select box with a more beautiful view. Its use is quite easy, but to display remote ajax data on select2 initially is quite confusing (makes me confused especially). In general, the problem that is often encountered in remote ajax select2 is templateResult: formatRepo and click not working in select2 remote ajax option. From both of these problems, I will try to give the solution in this article.

Tutorial Select2 Using AJAX With Example in Jquery, PHP, and MySQL.

Note: Use Select2 Version 4.0.0 for the application to run properly

1. Prepare the MySql database with “selectdb” name

2. Create a table with country name then fill in the code list and country name as shown below (to speed up the process, please download through my repository at https://github.com/seegatesite/country-db-mysql-list/blob/master/mysql_country_list.sql

3. I use PDO as PHP connection method with MySQL. Please create a class for database connections and classes to call MySql tables

dbconn.php

<?php
$dbuserx='root';
$dbpassx='123456';
class dbconn {
	public $dblocal;
	public function __construct()
	{

	}
	public function initDBO()
	{
		global $dbuserx,$dbpassx;
		try {
			$this->dblocal = new PDO("mysql:host=localhost;dbname=selectdb;charset=latin1",$dbuserx,$dbpassx,array(PDO::ATTR_PERSISTENT => true));
			$this->dblocal->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
		}
		catch(PDOException $e)
		{
			die("Tidak dapat konek ke database");
		}
	}
}
?>

<?php

$dbuserx='root';

$dbpassx='123456';

class dbconn {

public $dblocal;

public function __construct()

{

}

public function initDBO()

{

global $dbuserx,$dbpassx;

try {

$this->dblocal = new PDO("mysql:host=localhost;dbname=selectdb;charset=latin1",$dbuserx,$dbpassx,array(PDO::ATTR_PERSISTENT => true));

$this->dblocal->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

}

catch(PDOException $e)

{

die("Tidak dapat konek ke database");

}

sql.php

<?php
class sql extends dbconn {
	public function __construct()
	{
		$this->initDBO();
	}

	public function getAllCountry($term){
		$term = '%'.$term.'%';
		$db = $this->dblocal;
		try
		{
			$stmt = $db->prepare("SELECT a.* FROM country a WHERE a.country_name LIKE :term OR  a.country_code LIKE :term ");

			$stmt->bindParam("term",$term);
			$stmt->execute();
			$stat[0] = true;
			$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
			return $stat;
		}
		catch(PDOException $ex)
		{
			$stat[0] = false;
			$stat[1] = $ex->getMessage();
			return $stat;
		}
	}
}

<?php

class sql extends dbconn {

public function __construct()

{

$this->initDBO();

}

public function getAllCountry($term){

$term = '%'.$term.'%';

$db = $this->dblocal;

try

{

$stmt = $db->prepare("SELECT a.* FROM country a WHERE a.country_name LIKE :term OR a.country_code LIKE :term ");

$stmt->bindParam("term",$term);

$stmt->execute();

$stat[0] = true;

$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);

return $stat;

}

catch(PDOException $ex)

{

$stat[0] = false;

$stat[1] = $ex->getMessage();

return $stat;

}

4. Create an ajax.php file

<?php
header('Content-Type: application/json');
require_once ("dbconn.php");
require_once ("sql.php");
$term = trim(strip_tags($_GET['q']));
$sql = new sql();
$countrylist = $sql->getAllCountry($term);
$country = array();
$i = 0;
foreach ($countrylist[1] as $key) {
	$countrylist[1][$i]['desc'] = $key['country_code'].' - '.$key['country_name'];;
	$countrylist[1][$i]['id'] = $key['country_code'];
	$i++;
}
$country['items'] = $countrylist[1];
echo json_encode($country);
?>

<?php

header('Content-Type: application/json');

require_once ("dbconn.php");

require_once ("sql.php");

$term = trim(strip_tags($_GET['q']));

$sql = new sql();

$countrylist = $sql->getAllCountry($term);

$country = array();

$i = 0;

foreach ($countrylist[1] as $key) {

$countrylist[1][$i]['desc'] = $key['country_code'].' - '.$key['country_name'];;

$countrylist[1][$i]['id'] = $key['country_code'];

$i++;

}

$country['items'] = $countrylist[1];

echo json_encode($country);

5. Create an index.php file in the selectdb project folder and fill in the following code

<!DOCTYPE html>
<html>
<head>
	<title>Tutorial selectDB remote ajax using jquery,php and mysql by seegatesite.com</title>
	<link href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/css/select2.min.css" rel="stylesheet" />
</head>
<body>
	<h4>Tutorial selectDB remote ajax using jquery,php and mysql by <a href="https://seegatesite.com"></a>seegatesite.com</a></h4>
	<div>
		<select class="select2"></select>
	</div>
	<script
	src="https://code.jquery.com/jquery-2.2.4.js"
	integrity="sha256-iT6Q9iMJYuQiMWNd9lDyBUStIq/8PuOW33aOqmvFpqI="
	crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.3/js/select2.min.js"></script>
	<script type="text/javascript">
		$(".select2").select2({
			placeholder: "Search country here...",
			width: '175px',
			ajax: {
				url: "ajax.php",
				dataType: 'json',
				delay: 250,
				data: function (params) {
					return {
		          q: params.term, // search term
		          page: params.page
		      };
		  },
		  processResults: function (data, params) {
		  	params.page = params.page || 1;
		  	return {
		  		results: data.items,
		  		pagination: {
		  			more: (params.page * 30) < data.total_count
		  		}
		  	};
		  },
		  cache: false
		},
			// escapeMarkup: function (markup) { return markup; }, // let our custom formatter work
			minimumInputLength: 1,
			templateResult: formatRepo,
			templateSelection: formatRepoSelection
		});

		function formatRepo (repo) {
			if (repo.loading) return repo.text;
			return repo.desc;
		}

		function formatRepoSelection (repo) {
			return repo.desc || repo.text;
		}
	</script>
</body>
</html>

<!DOCTYPE html>

<html>

<head>

<title>Tutorial selectDB remote ajax using jquery,php and mysql by seegatesite.com</title>

</head>

<body>

<h4>Tutorial selectDB remote ajax using jquery,php and mysql by <a href="https://seegatesite.com"></a>seegatesite.com</a></h4>

<div>

</div>

<script

src="https://code.jquery.com/jquery-2.2.4.js"

integrity="sha256-iT6Q9iMJYuQiMWNd9lDyBUStIq/8PuOW33aOqmvFpqI="

crossorigin="anonymous"></script>

$(".select2").select2({

placeholder: "Search country here...",

width: '175px',

ajax: {

url: "ajax.php",

dataType: 'json',

delay: 250,

data: function (params) {

return {

q: params.term, // search term

page: params.page

};

processResults: function (data, params) {

params.page = params.page || 1;

return {

results: data.items,

pagination: {

more: (params.page * 30) < data.total_count

}

};

cache: false

// escapeMarkup: function (markup) { return markup; }, // let our custom formatter work

minimumInputLength: 1,

templateResult: formatRepo,

templateSelection: formatRepoSelection

});

function formatRepo (repo) {

if (repo.loading) return repo.text;

return repo.desc;

}

function formatRepoSelection (repo) {

return repo.desc || repo.text;

}

</script>

</body>

</html>

A brief description:

In order to select2 remote ajax to run properly, do not forget to create the format formatRepo and formatRepoSelection.
On the server side, json data must have an attribute named “id“

To download the source code of the project, please use the following download link. Please share this article using social media button below to open download link.

Hopefully, this tutorial help solves your problem using remote2 ajax select2.