Autocomplete Textbox Using PHP, MySQL and jQuery

Autocomplete search shows the users list of matching results while types into textbox. In this article we will explain to you how to implement autocomplete textbox using PHP, MySQL and PHP.

jQuery AJAX Autocomplete – Country Example, Autocomplete Textbox using jQuery, PHP and Mysql, AJAX autocomplete with jQuery, PHP, and JSON, Php mysql ajax search autocomplete, PHP - Ajax AutoComplete Search, autocomplete-php, Creating autocomplete for city search using PHP and MySQL, PHP Ajax Autocomplete Search from Database Example, autocomplete search php, bootstrap autocomplete ajax

Checkout more articles on PHP

Here, we’ll take one example of autocomplete search for city names that will be fetched from a database and suggested to users while typing into a textbox.

Example

Output - Autocomplete Textbox Using PHP, MySQL and jQuery - Clue Mediator

File Structure

autocomplete-textbox-ajax-php
- ajax-city-search.php
- connection.php
- index.php

1. Create table in database

First, we will create a table named `city` in the database. Run the following script to create a table with dummy records in the database.

# Create city table
CREATE TABLE `city` (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
city_name varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# Inserting data in the city table
INSERT INTO `city` (`id`, `city_name`) VALUES
(1, 'Alameda'),
(2, 'Calexico'),
(3, 'Belmont'),
(4, 'Chicago'),
(5, 'Los Angeles'),
(6, 'New York'),
(7, 'Detroit'),
(8, 'Las Vegas'),
(9, 'Portland'),
(10, 'Milwaukee');

2. Create a database connection

Now, we have to create a database connection file named `connection.php` to fetch the city name from the database.

connection.php

<?php
	$dbhost = 'localhost';
	$dbuser = 'root';
	$dbpass = '';
	$db = 'demo';
	$conn = mysqli_connect($dbhost, $dbuser, $dbpass , $db) or die($conn);
?>

3. Create an autocomplete search form

Here, we include the Jquery, Jquery UI and CSS file for autocomplete search and initialize the `autocomplete()` method.

index.php

<!doctype html>
<html lang="en">
<head>
<!-- jQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

<!-- jQuery UI -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.css" />
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>

<!-- Bootstrap CSS -->
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet">
</head>

<body>
<div class="container">
  <h5 class="mt-4">Autocomplete Textbox Using PHP, MySQL and jQuery - <a href="https://www.cluemediator.com/" target="_blank">Clue Mediator</a></h5>
  <div class="row">
     <div class="col-md-4">
     <label>City:</label>
     <input type="text" name="city" id="search_city" placeholder="Type to search..." class="form-control">
     </div>
  </div>
</div>

<script type="text/javascript">
  $(function() {
     $( "#search_city" ).autocomplete({
       source: 'ajax-city-search.php',
     });
  });
</script>

</body>
</html>

4. Create PHP code for search from database

The `autocomplete()` method is called the `ajax-city-search.php` file that fetches the data from database as user typing in textbox and returns in json encoded format. So here we write the PHP code as per GET request.

ajax-city-search.php

<?php
require_once('connection.php');

function get_city($conn , $term){
	$query = "SELECT * FROM city WHERE city_name LIKE '%".$term."%' ORDER BY city_name ASC";
	$result = mysqli_query($conn, $query);
	$data = mysqli_fetch_all($result,MYSQLI_ASSOC);
	return $data;
}

if (isset($_GET['term'])) {
	$getCity = get_city($conn, $_GET['term']);
	$cityList = array();
	foreach($getCity as $city){
		$cityList[] = $city['city_name'];
	}
	echo json_encode($cityList);
}
?>

5. Output

Run the example and check the output in the browser.

That’s it for today.
Thank you for reading. Happy Coding!

Demo & Source Code

GitHub Repository