jQuery UI autocomplete allows the user to select an item from the suggestion list based on the typed value.
You can load the 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.
Contents
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
<!-- 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
, setdataType
tojson
, and pass typed values asdata
. - On successful callback pass data to
response()
. - Using
select
event to display selected optionlabel
in the#autocomplete
andvalue
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
8. Conclusion
If the suggestion list is not displaying then use the browser network tab to debug.
Make sure the return response is in valid format otherwise, the data does not load properly.
You can view the MySQL version of this tutorial here.
If you found this tutorial helpful then don't forget to share.