Sunday, 20 June 2021

JavaScript Autocomplete Textbox from Database using PHP Ajax

Simple Autocomplete

Autocomplete with Recent Searches

The main functionality of Autocomplete Textbox is to allow user to speedy search data and select data from the auto suggested option list. Autocomplete Textbox has show the question result in pre-populated option list when the user has type something in input field. So build this Autocomplete feature for Search textbox, here we will use pure JavaScript with out using any existing library like jQuery or jQuery UI library. In this tutorial for display Autocomplete suggestion result we will use Bootstrap 4 CSS library. So by using JavaScript and Bootstrap 4 CSS, we will build Autocomplete feature, so when the use has start typing in the autocomplete textbox field, then this Autocomplete feature will send Ajax request for fetch match value from database and then after it has list them below textbox as Autocomplete suggestion and from suggestion user can choose value from that list.

Autocomplete Textbox has give us a user-friendly way to append a search input field with auto pre-populated dropdown data in the our web application. This Autocomplete Textbox feature is very useful when we want to display auto suggestion from Mysql Database when the user has start typing in the textbox. From this tutorial you can find how to implement Autocomplete textbox and display pre-populated suggestions from database using JavaScript, PHP and MySQL.

In this tutorial, we will build Auto complete input field for search Post title. So here Auto suggestion post title will be retrieved from MySQL database and listed under the textbox when user has been star type under this textbox field.

JavaScript Autocomplete Textbox from Database using PHP

Autocomplete with Recent Searches

In this Autocomplete Textbox tutorial, we have add one more feature like fill Autocomplete data with Recent Search data. So when user again come on the Search textbox, then they can first view their recent search history as autofill below search textbox. So suppose User again come for same search then they can directly click on recent search suggestion.

This is very interesting feature and it will plus point in your autocomplete textbox feature. This feature we can mostly see in Google site, Youtube site and other many more sites. So when we have again visit this sites and go to search textbox then it has firstly display our recent history data as auto suggestion. So this feature we have to made under this tutorial.

Make MySQL Table

For store auto suggestion data, first we need to create a table in our database. For this first run following .sql script, which will create post table in MySQL database.


--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `post`
--

CREATE TABLE `post` (
  `id` mediumint(8) UNSIGNED NOT NULL,
  `post_title` text,
  `post_description` text,
  `author` varchar(255) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `post_image` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `post`
--
ALTER TABLE `post`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `post`
--
ALTER TABLE `post`
  MODIFY `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT;

View Demo

Download Source Code

Create An Autocomplete Search Form

Below you can find HTML code for build Autocomplete Search Textbox. Here we can see that, we have add only Bootstrap 4 CSS library link only.


<!DOCTYPE HTML>
<html>
<head>
	<meta charset="utf-8" />
	<title>Autocomplete with Recent Searches using JavaScript PHP MySQL</title>
	<meta name="viewport" content="width=device-width, initial-scale=1" />
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    <link rel="stylesheet" href="https://pro.fontawesome.com/releases/v5.10.0/css/all.css" integrity="sha384-AYmEC3Yw5cVb3ZcuHtOA93w35dYTsvhLPVnYs9eStHfGJvOvKxVfELGroGkvsg+p" crossorigin="anonymous"/>
</head>
<body>
    <div class="container">
    	<h2 class="text-center mt-4 mb-4">Autocomplete with Recent Searches using JavaScript PHP MySQL</h2>
    	<div class="row mt-5 mb-5">
    		<div class="col col-sm-2">&nbsp;</div>
    		<div class="col col-sm-8">
    			<div class="dropdown">
    				<input type="text" name="search_box" class="form-control form-control-lg" placeholder="Type Here..." id="search_box" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false" onkeyup="javascript:load_data(this.value)" onfocus="javascript:load_search_history()" />
    				<span id="search_result"></span>
    			</div>
    		</div>
    	</div>    	
    </div>
</body>
</html>

Here under this HTML code we can see that in input text field with name search_box, we can see that JavaScript load_data() function has been called on keyup event, so when user has been type something in textbox then this function has been called and it has fetch data from database and display as pre-populated suggestion result below textbox. Below you can find javascript also.


function load_search_history()
{
	var search_query = document.getElementsByName('search_box')[0].value;

	if(search_query == '')
	{

		fetch("process_data.php", {

			method: "POST",

			body: JSON.stringify({
				action:'fetch'
			}),

			headers:{
				'Content-type' : 'application/json; charset=UTF-8'
			}

		}).then(function(response){

			return response.json();

		}).then(function(responseData){

			if(responseData.length > 0)
			{

				var html = '<ul class="list-group">';

				html += '<li class="list-group-item d-flex justify-content-between align-items-center"><b class="text-primary"><i>Your Recent Searches</i></b></li>';

				for(var count = 0; count < responseData.length; count++)
				{

					html += '<li class="list-group-item text-muted" style="cursor:pointer"><i class="fas fa-history mr-3"></i><span onclick="get_text(this)">'+responseData[count].search_query+'</span> <i class="far fa-trash-alt float-right mt-1" onclick="delete_search_history('+responseData[count].id+')"></i></li>';

				}

				html += '</ul>';

				document.getElementById('search_result').innerHTML = html;

			}

		});

	}
}

function get_text(event)
{
	var string = event.textContent;

	//fetch api

	fetch("process_data.php", {

		method:"POST",

		body: JSON.stringify({
			search_query : string
		}),

		headers : {
			"Content-type" : "application/json; charset=UTF-8"
		}
	}).then(function(response){

		return response.json();

	}).then(function(responseData){

		document.getElementsByName('search_box')[0].value = string;
	
		document.getElementById('search_result').innerHTML = '';

	});

	

}

function load_data(query)
{
	if(query.length > 2)
	{
		var form_data = new FormData();

		form_data.append('query', query);

		var ajax_request = new XMLHttpRequest();

		ajax_request.open('POST', 'process_data.php');

		ajax_request.send(form_data);

		ajax_request.onreadystatechange = function()
		{
			if(ajax_request.readyState == 4 && ajax_request.status == 200)
			{
				var response = JSON.parse(ajax_request.responseText);

				var html = '<div class="list-group">';

				if(response.length > 0)
				{
					for(var count = 0; count < response.length; count++)
					{
						html += '<a href="#" class="list-group-item list-group-item-action" onclick="get_text(this)">'+response[count].post_title+'</a>';
					}
				}
				else
				{
					html += '<a href="#" class="list-group-item list-group-item-action disabled">No Data Found</a>';
				}

				html += '</div>';

				document.getElementById('search_result').innerHTML = html;
			}
		}
	}
	else
	{
		document.getElementById('search_result').innerHTML = '';
	}
}

In this JavaScript code, we have make two function get_text() and load_data() function. Here get_text() function will be called when we have click on any list of Autocomplete data, then that option value will be display in textbox field and auto-suggestion result will be removed from web page and load_data() function has fetch data from database and display result in Autocomplete textbox using Ajax.

Retrieve Autocomplete Data from Database with PHP and MySQL

In this tutorial, we have write php script in process_data.php file. So this PHP script code will be called by JavaScript load_data() function using Ajax. This file has been fetch post title data based on the search textbox query and this file has return back data in JSON string format using PHP with Mysql database.

The following PHP code has been retrieved data from from MySQL database and filter data by $_POST["query"] variable value and after this filter post title data has been returned back to javascript load_data() function in JSON string format.


<?php

//process_data.php

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

if(isset($_POST["query"]))
{	

	$data = array();

	$condition = preg_replace('/[^A-Za-z0-9\- ]/', '', $_POST["query"]);

	$query = "
	SELECT post_title FROM post 
		WHERE post_title LIKE '%".$condition."%' 
		ORDER BY id DESC 
		LIMIT 10
	";

	$result = $connect->query($query);

	$replace_string = '<b>'.$condition.'</b>';

	foreach($result as $row)
	{
		$data[] = array(
			'post_title'		=>	str_ireplace($condition, $replace_string, $row["post_title"])
		);
	}

	echo json_encode($data);
}

$post_data = json_decode(file_get_contents('php://input'), true);

if(isset($post_data['search_query']))
{

	$data = array(
		':search_query'		=>	$post_data['search_query']
	);

	$query = "
	SELECT search_id FROM recent_search 
	WHERE search_query = :search_query
	";

	$statement = $connect->prepare($query);

	$statement->execute($data);

	if($statement->rowCount() == 0)
	{
		$query = "
		INSERT INTO recent_search 
		(search_query) VALUES (:search_query)
		";

		$statement = $connect->prepare($query);

		$statement->execute($data);
	}

	$output = array(
		'success'	=>	true
	);

	echo json_encode($output);

}

if(isset($post_data['action']))
{
	if($post_data['action'] == 'fetch')
	{
		$query = "SELECT * FROM recent_search ORDER BY search_id DESC LIMIT 10";

		$result = $connect->query($query);

		$data = array();

		foreach($result as $row)
		{

			$data[] = array(
				'id'				=>	$row['search_id'],
				'search_query'		=>	$row["search_query"]
			);
		}

		echo json_encode($data);
	}

}
?>

Lastly, From this tutorial you can find the solution of How to make Autocomplete Textbox using JavaScript with PHP and Mysql Database.

If you want to get complete source with .sql file, so please write your email address in comment box. We will send you complete source code file at your define email address.

View Demo

Download Source Code