PHP MySQL ajax search autocomplete example
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 users 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
How to search live data using ajax jquery | Jquery ajax search example
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 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, 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 PHP script to be executed by the MySQL server and the response is returned by the server as ‘JSON’ with the help of 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 a 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 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, How to upload image in PHP and store in database and folder
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.
