Loading data from PostgreSQL in Select2 with PHP

Select2 jQuery plugin which makes the HTML select element more user-friendly.

It allows loading data with and without AJAX.

In this tutorial, I show how you can dynamically load data from PostgreSQL database in select2 using jQuery AJAX and PHP.

---

Contents

1. Table structure
2. Configuration
3. Download and Include
4. HTML
5. PHP
6. jQuery
7. Output
8. Conclusion

---

1. Table structure

I am using users table in the example. It has the following structure –

CREATE TABLE users (
     id serial PRIMARY KEY,
     username varchar(80) NOT NULL,
     fullname varchar(80) NOT NULL,
     email varchar(80) NOT NULL
)

---

2. Configuration

Create a config.php for database configuration.

Completed Code

<?php

$host = "localhost";
$user = "postgres";
$password = "root";
$dbname = "tutorial";
$con = pg_connect("host=$host dbname=$dbname user=$user password=$password");

if (!$con) {
   die('Connection failed.');
}

---

3. Download and Include

• Download the library from here.
• Include select2.min.css and select2.min.js files with the jQuery library. 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.0/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 <select id='selUser'> element.

Completed Code

<select id='selUser' style='width: 200px;'>
     <option value='0'>- Search user -</option>
</select>

---

5. PHP

Create ajaxfile.php file.

Check if searchTerm is POST or not. If not POST then fetch all records from the users table otherwise fetch records from users table where $search is found in fullname field.

Assign fetched records to $result.

Loop on the fetched records and initialize $data Array with id and text keys. Pass $id in id key and $fullname in text key.

Return $data Array in JSON format.

Completed Code

<?php
include 'config.php';

$result = array();
if(!isset($_POST['searchTerm'])){ 
      $sql = "select * from users order by fullname";
      $result = pg_query($con, $sql);
}else{ 
      $search = $_POST['searchTerm']; 
 
      $sql = "select * from users where fullname ilike $1";
      $result = pg_query_params($con, $sql, array('%'.$search.'%'));
}

$data = array();

while ($row = pg_fetch_assoc($result) ){

      $id = $row['id'];
      $fullname = $row['fullname'];

      $data[] = array(
           "id" => $id, 
           "text" => $fullname
      );

}

echo json_encode($data);
die;

---

6. jQuery

Define select2 on #selUser.

Send AJAX request using ajax option. Set its url: 'ajaxfile.php', type: 'post', dataType: 'json', pass the typed value as data.

Handle successful callback using processResults.

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
            }
      });
});

---

7. Output

View Output

---

8. Conclusion

Returned response from AJAX must have id and text keys otherwise, data does not load properly.

If you have too many records then you can use LIMIT in SQL query to fetch the limited number of records at a time.

If you found this tutorial helpful then don't forget to share.