PHP MySQL ajax search autocomplete example

by

In this tutorial, we will go through a PHP MySQL ajax search autocomplete example or jquery ajax search example. Here I am using a table named ‘users’ as we have to fetch the data from the user’s table to perform the search operation. In order to do the search successfully, we need to know three things basically

      1. JQUERY
      2. AJAX
      3. PHP SCRIPT

PHP MySQL ajax search autocomplete

Illustration of PHP MySQL ajax search autocomplete 

Here, we will be using a text search box to search the data from the database. When a user types in the search box, we will get the value with the help of onkeyup function whatever text is typed by the user.

Now we need to know about the ajax request. The ajax request requires four parameters to send the data. The parameters are mentioned below—

     1. URL:- This is the name of the PHP file where the ajax request is sent.

     2. TYPE:- This includes the method name of the request i.e. GET method or POST method.

     3. DATA:- The data sent to the ajax request. For eg:- In this tutorial, the search value is considered as the data.

     4. DATATYPE:- This means the response type of the ajax request returned by the MySQL server. Here, we will use the datatype as ‘JSON’.

So, after getting the search value from the text box, we send this value to the ajax request as the data parameter. Then the data will be sent to the PHP script to be executed by the MySQL server and the response is returned by the server as ‘JSON’ with the help of the json_encode() function.

Also, Read Log in and Log out using session in PHP and MySQLi

Let us create a table in the database. I am using the table name “users”.

DDL information of the table
———————————-
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_no` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Complete Code:-

<?php
include('connect.php');
if(isset($_POST['search'])){
    $key = $_POST['search'];
	$stmt = $con->prepare("select * from users where username like '%$key%' or phone_no like '%$key%'");
	$stmt->execute();
	$user_details = $stmt->fetch(PDO::FETCH_ASSOC);
	if($stmt->rowCount()>0)
	{
	   $output['success'] = '<table class="table table-bordered">
	
					<thead>
						<th>Sl No.</th>
						<th>Username</th>
						<th>Phone No</th>
						<th>Email</th>
						<th>Password</th>
					</thead>
					<tbody>
						<tr>
						  <td>'.$user_details['id'].'</td>
						  <td>'.$user_details['username'].'</td>
						  <td>'.$user_details['phone_no'].'</td>
						  <td>'.$user_details['email'].'</td>
						  <td>'.$user_details['password'].'</td>
						</tr>
					</tbody>
		
				</table>';
	echo json_encode($output);
	}
	else
	{
		$output['failed'] = '<font color="#ff0000" style="font-size: 20px;">Data not available</font>';
		echo json_encode($output);
	}
	exit;
	}
?>
<html>
<head>
<title>ajax example</title>
<link rel="stylesheet" href="bootstrap.css" crossorigin="anonymous">
<!-- Optional theme -->
<link rel="stylesheet" href="bootstrap-theme.css" crossorigin="anonymous">
<style>
.container{
	width:50%;
	height:30%;
	padding:20px;
}
</style>
</head>

<body>
	<div class="container">
	<h3><u>How to search data from database using ajax jquery in PHP</u></h3>
	<br/><br/>
	<div class="row">
		<div class="col-sm-8">
			<div class="form-group">
			    <label for="name">Search information:</label>
			    <input type="search" class="form-control" onkeyup="fetchData()" id="find" placeholder="fill the information">
			    
			</div>
		</div>
				
	</div>
	<br/><br/>
	<h3><u>Search Result</u></h3><br/>
	<div id="comehere"></div>
    </div>

<script src="jquery-3.2.1.min.js"></script>
<script src="bootstrap.min.js"></script>

<script>
	function fetchData(){
		var search = $('#find').val();
		//alert(search);
		if(search){
			$.ajax({
			 url: 'ajaxsearch.php',
			 type: 'post',
			 data: {
			 	'search': search
			 },
			 dataType: 'json'
			})
			.done(function(data){
				if(data.success)
				{
					$('#comehere').html(data.success);
				}
				else
				{
					$('#comehere').html(data.failed);
				}
			})
			.fail(function(data){
				alert(data);
			});
		}
		else{
			$('#comehere').html('');
		}
	}
</script>

</body>
</html>

NOTE*
——–
Download the bootstrap CSS and js files from google and include the path of the files in the href attribute of the link tag and src attribute of the script tag respectively. In the very first line of the PHP script, I have included the database connection file.

Also read, Upload Image in PHP MySQL Database and display

CONCLUSION:- I hope this article will help you to search the live data using ajax jquery in PHP. If you have any doubt then please leave your comment below.


Leave a Comment