To learn how to implement ajax live search in PHP and MySQL, you need to follow the blog below or follow the video link or download the source code from Github.
Video Link
Step 1: Create a file with boilerplate and table
So firstly, you need to create a file containing a boiler plate along with a table where data will be shown (let’s suppose index.php).
Here, I am using Bootstrap for saving time for styling.
<!-- Index.php -->
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Searching demo</title>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-gH2yIJqKdNHPEq0n4Mqa/HGKIhSkIHeL5AyhkYV8i59U5AR6csBvApHHNl/vI1Bx" crossorigin="anonymous">
</head>
<body class="bg-dark text-light">
<div class="container">
<div class="row">
<div class="col-md-8 mx-auto mt-5">
<h3 class="mb-4">Live Data Search in PHP using AJAX (Vanilla JavaScript)</h3>
<table class="table table-light table-striped">
<thead>
<tr>
<th>ID</th>
<th>VALUES</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-A3rJD856KowSb7dwlZdYEkO39Gagi7vIsF0jrRAoQmDKKtQBHUuLZ9AsSv4jD4Xa" crossorigin="anonymous"></script>
</body>
</html>Step 2: Add a search input field
Before the table, you need to add some codes for an input field where you can type something that you want to search.
Then add an Id to tbody tag.
<!-- Index.php -->
<h3 class="mb-4">Live Data Search in PHP using AJAX (Vanilla JavaScript)</h3>
<!-- Search Input Field Code Start-->
<div class="mb-3">
<label class="form-label fw-bold">Search</label>
<input type="text" class="form-control" placeholder="Type anything...">
</div>
<!-- Search Input Field Code End -->
<table class="table table-light table-striped">
<thead>
<tr>
<th>ID</th>
<th>VALUES</th>
</tr>
</thead>
<tbody id="table_data">
</tbody>
</table>Step 3: Fetch Data using AJAX from another file
Create a file where ajax requests will be made to fetch data (let’s suppose server.php).
Write the ajax code in index.php before closing the body tag to simply fetch data.
What the code will do is:
It will do a GET request to another file named “server.php” asynchronously to retrieve data from the server and then put it in the tbody tag by accessing the tbody tag of the table by its id.
When the request will be in progress a loader will be shown and as it gets response, the data will be shown.
<!-- Index.php -->
<script>
function load_data()
{
let xhr = new XMLHttpRequest();
xhr.open("GET","server.php",true);
xhr.onprogress = function(){
document.getElementById('table_data').innerHTML = `<div class="spinner-border" role="status">
<span class="visually-hidden">Loading...</span>
</div>`;
}
xhr.onload = function(){
document.getElementById('table_data').innerHTML = xhr.responseText;
}
xhr.send();
}
load_data();
</script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-A3rJD856KowSb7dwlZdYEkO39Gagi7vIsF0jrRAoQmDKKtQBHUuLZ9AsSv4jD4Xa" crossorigin="anonymous"></script>
</body>
</html>Step 4: Write server-side code to fetch data
Here you will be connecting to the database and fetch data from a table.
<!-- server.php -->
<?php
// connect to your database
$con = mysqli_connect("localhost","root","","test");
$query = "SELECT * FROM `data`";
$result = mysqli_query($con,$query);
if(mysqli_num_rows($result)==0){
echo "<tr><td colspan='2'>No records found!</td></tr>";
exit;
}
while($data = mysqli_fetch_assoc($result))
{
echo"<tr>
<th>$data[id]</th>
<td>$data[name]</td>
</tr>";
}
?>Step 5: Its time for live data search implementation
Add oninput event listener attribute on the search input field that you have created earlier. Inside it, you have to call the load_data() which you were using to fetch data using ajax.
But this time you need to pass the value of input field to the load_data().
<!-- Index.php -->
<div class="mb-3">
<label class="form-label fw-bold">Search</label>
<input type="text" class="form-control" oninput="load_data(this.value)" placeholder="Type anything...">
</div>The above code simply means that if you will enter any data or press even a character then load_data() will be called.
Then we will need a variable as a parameter in load_data() to receive the value when oninput event listener will call the load_data() function.
Its default value will be blank i.e., single quotes only.
And you will also need to pass the search variable to the server.php page as a parameter.
<!-- Index.php -->
<script>
function load_data(search='')
{
let xhr = new XMLHttpRequest();
xhr.open("GET","server.php?search="+search,true);
.............
xhr.send();
}
load_data();
</script>Then on server.php file you need to access the variable from $_GET method and use LIKE Operator to search and filter data from MySQL database.
<!-- server.php -->
<?php
// connect to your database
$con = mysqli_connect("localhost","root","","test");
// get the index search and store it in a variable
$search = $_GET['search'];
// add like operator with the query and use this pattern
$query = "SELECT * FROM `data` WHERE name LIKE '%$search%'";
// for multi column search filter
// $query = "SELECT * FROM `data` WHERE name LIKE '%$search%' OR id LIKE '%$search%'";
$result = mysqli_query($con,$query);
.....All done! Hope it would help you.
Click to know more about LIKE Operator
