PHP MySQL Ajax Autocomplete TextBox From DB Example

Hello PHP developers, autocomplete or auto fill search box in php mysql using jQuery ui and ajax. In this post, i will show you step by step on how to autocomplete textbox using jquery php and mysql πŸ’‘ like google autocomplete textbox πŸ”₯ πŸ”₯.

In this php autocomplete textbox from database example, i will use jquery, ajax, json with PHP MySQL.

PHP MySQL Ajax Autocomplete textbox using jQuery UI

The following autocomplete textbox using jquery php and mysql πŸ’‘ like google autocomplete textbox πŸ”₯ πŸ”₯code will search data from database and display without refresh/reloading whole web page.

Step 1 – Create PHP APP Directory

Step 2 – Create phpmyadmin MySQL Database and Connecting File

Step 3 – Create Autocomplete Search HTML Form

Step 4 – Create Ajax Autocomplete Search From Database File

Step 1 – Create PHP APP Directory

In step 1, Navigate to your local web server directory. And inside this directory, create one directory. And you can name this folder anything.

Here, I will “demo” the name of this folder. Then open this folder in any text editor (i will use sublime text editor).

Step 2 – Create phpmyadmin MySQL Database and Connecting File

In step 2, you need to create database and table. So run the following sql query to create database and table:

Then create a php file that named mydbCon. Which is used to connect phpmyadmin mysql database to project (demo).

So, now create mydbCon file and add the below given code into your file:

CREATE DATABASE my_db;


CREATE TABLE `customers` (
  `id` int(10) UNSIGNED NOT NULL,
  `fname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Step 3 – Create Autocomplete Search HTML Form

In step 3, create a php file that named autocomplete-search.php. This file will display autocomplete textbox using jquery php and mysql like google autocomplete textbox.

Here, i will use jQuery ui with ajax to auto fill search data from database without realoding the whole web page. Now, you need to add the following ajax live search html form code into autocomplete-search.php file:

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Autocomplete textbox using jquery php and mysql like google autocomplete textbox</title>
 
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.css" />
 
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">

  <script src="https://code.jquery.com/jquery-3.4.1.js"></script> 

  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>

<body> 
<div class="container mt-4">
  <div class="row">
     <h2>PHP AJAX Autocomplete Textbox From Database Example</h2>
     <input type="text" name="autocomplete-search" id="autocomplete-search" placeholder="search here...." class="form-control">  
  </div>
</div>
<script type="text/javascript">
  $(function() {
     $( "#autocomplete-search" ).autocomplete({
       source: 'ajax-autocomplete-search-data.php',
     });
  });
</script>
</body>
</html>

Step 4 – Create Ajax Autocomplete Search From Database File

In step 4, create new file that named ajax-autocomplete-search.php file. Because this is used to autocomplete search form data into mysql database and return response in JSON format.

So, add the below code into your ajax-autocomplete-search.php file:

<?php
include 'mydbCon.php';

$term = mysqli_real_escape_string($dbCon,$_GET['term']);

$sql = "SELECT * FROM customers WHERE fname LIKE '$term%'";

$query = mysqli_query($dbCon, $sql);

$result = [];

while($data = mysqli_fetch_array($query))
{
	$result[] = $data['fname'];
}
echo json_encode($result);
?>

The autocomplete textbox in php mysql with ajax will look like in following image:

Recommended PHP Tutorials

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *