save.bz
Search

Live Search in PHP Mysqli using jQuery AJAX

1 week ago  •  1K views
Live Search in PHP Mysqli using jQuery AJAX

1. 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', '[email protected]', '59c95189ac895fcc1c6e1c38d067e244', 'uttar pradesh', 161975),
(46, 'shiv ', 'shankar', '[email protected]', '59c95189ac895fcc1c6e1c38d067e244', 'uttar pradesh', 0),
(47, 'ramesh', 'kumar', '[email protected]', '59c95189ac895fcc1c6e1c38d067e244', 'mumbai', 0),
(48, 'sanjay ', 'kumar', '[email protected]', '59c95189ac895fcc1c6e1c38d067e244', 'delhi', 0),
(49, 'madan', 'kumar', '[email protected]', 'fd68e8922a6705a916b19669fb356cce', 'delhi', 0),
(50, 'aman ', 'kumar', '[email protected]', '59c95189ac895fcc1c6e1c38d067e244', 'mumbai', 0),
(51, 'manish', 'pushpakar', '[email protected]', '59c95189ac895fcc1c6e1c38d067e244', 'delhi', 0);

2. Create MySql Connection in PHP

For importing and exporting database in MySql will make a separate file ‘dbCongif.php’.

PHP: 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);
}
?>




3. 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.

PHP: 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>


4. 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.

PHP: 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>";
}
?>