In this post we are going to learn Live search in PHP Mysqli using jQuery AJAX.On most websites, we see a search bar on the website and we can search for content within it. If you use Facebook or Twitter, there is an outstanding direct search feature that you can use to find new friends or followers.
Suppose we enter text on this site to find it. After that, we can get instant results from the site without refreshing the page. This function is performed by Ajax with Jquery. With the help of Jquery we can use the Ajax http dom function.
Using this function, it searches data from the server and sends the results back to the front end of the website without refreshing the page. This function gives your website a stunning display. I have provided the source code with this post. I hope you learn these things.
Output
Creating the Database Table
The first step in this tutorial is the creation of a MySQL database. you can create tables by running SQL queries. Create a table ‘students’ in database using the following SQL query.
-- -- Database: `php_ajax_testing` -- -- Table structure for table `students` -- CREATE TABLE `students` ( `id` int(50) NOT NULL PRIMARY KEY AUTO_INCREAMENT, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `password` varchar(100) NOT NULL, `city_name` varchar(100) NOT NULL, `otp` int(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `students` -- INSERT INTO `students` (`id`, `first_name`, `last_name`, `email`, `password`, `city_name`, `otp`) VALUES (36, 'manish', 'pushpakar', 'manishpushpakar007@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'uttar pradesh', 161975), (46, 'shiv ', 'shankar', 'shiv07@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'uttar pradesh', 0), (47, 'ramesh', 'kumar', 'ramesh007@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'mumbai', 0), (48, 'sanjay ', 'kumar', 'sanjay007@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'delhi', 0), (49, 'madan', 'kumar', 'madan007@gmail.com', 'fd68e8922a6705a916b19669fb356cce', 'delhi', 0), (50, 'aman ', 'kumar', 'aman007@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'mumbai', 0), (51, 'manish', 'pushpakar', 'manis1319@gmail.com', '59c95189ac895fcc1c6e1c38d067e244', 'delhi', 0);
Create MySql Connection in PHP
For importing and exporting database in MySql will make a separate file ‘dbCongif.php’. Add the following code and replace the database credentials with yours. You can find your db credentials in Application Access details:
dbCongif.php
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "php_ajax_testing";
// Create database connection
$con = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($con->connect_error) {
die("Connection failed: " . $con->connect_error);
}
?>Creating the search Form and jQuery AJAX
In this step, we have to implement the live search eventually. We will use the Bootstrap UI for styling the search component and also import the jQuery through CDN. Use the AJAX to make the POST request to fetch the data from the database.
Create the index file, and update the below code in the index.php file.
index.php
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Live Search in PHP Mysqli using jQuery AJAX</title>
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="directory/bootstrap.min.css">
<script src="directory/bootstrap.min.js"></script>
<script src="directory/jquery-3.4.1.js"></script>
</head>
<body>
<div class="container" style="margin-top: 50px;">
<h2 style="text-align: center;"><b>Live Search in PHP Mysqli using jQuery AJAX</b></h2><br>
<div class="row">
<div class="col-md-2"></div>
<div class="col-md-8 form-group">
<input type="text" id="search" class="form-control" autocomplete="off" placeholder="Search here.."><br>
</div>
<div class="col-md-2"></div>
</div>
<div class="result">
</div>
</div>
</body>
</html>
<!---jQuery ajax live search --->
<script type="text/javascript">
$(document).ready(function(){
// fetch data from table without reload/refresh page
loadData();
function loadData(query){
$.ajax({
url : "action.php",
type: "POST",
chache :false,
data:{query:query},
success:function(response){
$(".result").html(response);
}
});
}
// live search data from table without reload/refresh page
$("#search").keyup(function(){
var search = $(this).val();
if (search !="") {
loadData(search);
}else{
loadData();
}
});
});
</script>Processing Search Query in Backend
And here’s the source code of our “action.php” file which searches the database based on query string sent by the Ajax request and send the results back to browser.
The SQL SELECT statement is used in combination with the LIKE operator to find the matching records in countries database table. We’ve implemented the prepared statement for better search performance as well as to prevent the SQL injection attack.
action.php
<?php
// include database connection file
include "dbConfig.php";
// fetch data from student table..
$output = "";
if (isset($_POST['query'])) {
$search = mysqli_real_escape_string($con, $_POST['query']);
$sql = "SELECT * FROM students WHERE first_name LIKE '%$search%' || last_name LIKE '%$search%' ||
email LIKE '%$search%' || city_name LIKE '%$search%'";
}else{
$sql = "SELECT * FROM students ORDER BY id DESC";
}
$query = mysqli_query($con, $sql);
if (mysqli_num_rows($query) > 0) {
$output .= "<table class='table table-hover table-striped'>
<thead>
<tr>
<th>Id</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Email</th>
<th>City Name</th>
</tr>
</thead>";
while ($row = mysqli_fetch_assoc($query)) {
$output .= "<tbody>
<tr>
<td>{$row['id']}</td>
<td>{$row['first_name']}</td>
<td>{$row['last_name']}</td>
<td>{$row['email']}</td>
<td>{$row['city_name']}</td>
</tr>
</tbody>";
}
$output .="</table>";
echo $output;
}else{
echo "<h5>No record found</h5>";
}
?>Conclusion
Through out this PHP MySQL example, we described how to create live data search in PHP using AJAX quickly; we also shared how to retrieve data from the MySQL database and display data results in live search.
You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions / problems about this tutorial, please comment on the form below.😊
