PHP Ajax Autocomplete Search from Database Example

May 16, 2018 | Category : MySQL Ajax JQuery Bootstrap PHP

in this article, we will create ajax search autocomplete using php mysql and jquery. we will use jquery ajax for autocomplete with textbox.

As we know in today. ajax is more use in php development and also other language project. ajax make quick response without page refresh. so in this example we will create free script of ajax autocomplete search with php mysql database.

so basically. you need to create database with "users" table. after create users table you need to add some dummy users in your table. then we will create index file and write code of jquery and ajax on it. another file we will create for ajax request. So just follow bellow step to create full example.

Step 1: Create users table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

Step 2: Create index.php file

<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>Autocomplete search using PHP, MySQLi, Ajax and jQuery</title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

	<style type="text/css">
		.gst20{
			margin-top:20px;
		}
		#hdTuto_search{
			display: none;
		}
		.list-gpfrm-list a{
			text-decoration: none !important;
		}
		.list-gpfrm li{
			cursor: pointer;
			padding: 4px 0px;
		}
		.list-gpfrm{
			list-style-type: none;
    		background: #d4e8d7;
		}
		.list-gpfrm li:hover{
			color: white;
			background-color: #3d3d3d;
		}
	</style>
</head>
<body>
<div class="container">
	<h1 class="text-center gst20">Auto Complete Search using jQuery</h1>
	<div class="row justify-content-center gst20">
		<div class="col-sm-6">
			<form id="hdTutoForm" method="POST" action="">
				<div class="input-gpfrm input-gpfrm-lg">
				  	<input type="text" id="querystr" name="querystr" class="form-control" placeholder="Search Name" aria-describedby="basic-addon2">
				</div>
			</form>
			<ul class="list-gpfrm" id="hdTuto_search"></ul>
		</div>
	</div>
</div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.js"></script>
<script type="text/javascript">
	$(document).ready(function(){
	//Autocomplete search using PHP, MySQLi, Ajax and jQuery
		//generate suggestion on keyup
		$('#querystr').keyup(function(e){
			e.preventDefault();
			var form = $('#hdTutoForm').serialize();
			$.ajax({
				type: 'POST',
				url: 'do_search.php',
				data: form,
				dataType: 'json',
				success: function(response){
					if(response.error){
						$('#hdTuto_search').hide();
					}
					else{
						$('#hdTuto_search').show().html(response.data);
					}
				}
			});
		});

		//fill the input
		$(document).on('click', '.list-gpfrm-list', function(e){
			e.preventDefault();
			$('#hdTuto_search').hide();
			var fullname = $(this).data('fullname');
			$('#querystr').val(fullname);
		});
	});
</script>
</body>
</html>

Step 3: Create do_search.php file

<?php

	if(isset($_POST['querystr'])){
		$conn = new mysqli('localhost', 'root', 'root', 'laravel_test');

		$results = array('error' => false, 'data' => '');
 
		$querystr = $_POST['querystr'];
 
		if(empty($querystr)){
			$results['error'] = true;
		}else{
			$sql = "SELECT * FROM users WHERE name LIKE '%$querystr%'";
			$sqlquery = $conn->query($sql);
 
			if($sqlquery->num_rows > 0){
				while($ldata = $sqlquery->fetch_assoc()){
					$results['data'] .= "
						<li class='list-gpfrm-list' data-fullname='".$ldata['name']." ".$ldata['sirname']."'>".$ldata['name']." ".$ldata['sirname']."</li>
					";
				}
			}
			else{
				$results['data'] = "
					<li class='list-gpfrm-list'>No found data matches Records</li>
				";
			}
		}
 
		echo json_encode($results);
	}
?>

Now you can run and check....

Do you like below Tutorials ?

New Articles

Blog Categories

Random Articles