Create jQuery UI autocomplete with PostgreSQL PHP and AJAX

jQuery UI autocomplete allows user to select an item from the suggestion list based on the typed value.

You can load suggestion list with and without AJAX.

In this tutorial, I show how you can add jQuery UI autocomplete on your page and load PostgreSQL database data using AJAX and PHP.

Download

---

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.

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 new config.php file.

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 jQuery and jQuery UI libraries.
• Include jQuery and jQuery UI library script –

 <!-- CSS -->
 <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/themes/smoothness/jquery-ui.css">

 <!-- Script -->
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
 <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>

---

4. HTML

Create 2 text elements –

• 1st is used to initialize jQuery UI autocomplete.
• 2nd is used to display the selected item value from the suggestion list.

Completed Code

<!-- For defining autocomplete -->
Search User : <input type="text" id='autocomplete'> <br><br>

<!-- For displaying selected option value from autocomplete suggestion -->
Selected UserID : <input type="text" id='selectuser_id' readonly>

---

5. PHP

Create ajaxfile.php file to handle jQuery UI AJAX requests.

Check if search is POST or not.

If not POST then fetch all records from users table and assign to $result otherwise, search on fullname field and assign fetched records to $result.

Loop on $result and initialize $data Array with value and label keys.

Store $id in value and $fullname in label.

Return $data in JSON format.

Completed Code

<?php
include 'config.php';

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

      $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(
         "value" => $id, 
         "label" => $fullname
     );

}

echo json_encode($data);
die;

---

6. jQuery

Initialize autocomplete on #autocomplete.

• Use source option to load autocomplete data using jQuery AJAX.
• Send AJAX POST request to ajaxfile.php, set dataType to json, and pass typed values as data.
• On successful callback pass data to response().
• Using select event to display selected option label in the #autocomplete and value in #selectuser_id input fields.

Completed Code

$(document).ready(function(){

   // Single Select
   $( "#autocomplete" ).autocomplete({
        source: function( request, response ) {

             // Fetch data
             $.ajax({
                  url: "ajaxfile.php",
                  type: 'post',
                  dataType: "json",
                  data: {
                       search: request.term
                  },
                  success: function( data ) {
                       response( data );
                  }
             });
        },
        select: function (event, ui) {
             // Set selection
             $('#autocomplete').val(ui.item.label); // display the selected text
             $('#selectuser_id').val(ui.item.value); // save selected id to input
             return false;
        }
    });

});

---

7. Output

View Output

---

8. Conclusion

If the suggestion list not displaying then use the browser network tab to debug.

Make sure the return response is in valid format otherwise, data does not load properly.

You can view the MySQL version of this tutorial here.

Share this post