Ajax live data search in PHP and MySQL
- Tech Area
- October 13, 2023
In this tutorial, We will learn how to search data using Jquery AJAX in PHP and MySQL. We are using text field to live data search from mysql database on keyup using AJAX.
Files used in this tutorial:
1- connection.php (database connection file)
2- index.php (table and AJAX script)
3- show-data.php (fetch and show data from database)
4- search-data.php (search data from database)
Below are the step by step process of how to live search data using Jquery AJAX.
Step 1: Create a Database connection
In this step, create a new file connection.php to create database connection.
connection.php
<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "college_db";
$connection = mysqli_connect("$server","$username","$password");
$select_db = mysqli_select_db($connection, $database);
if(!$select_db)
{
echo("connection terminated");
}
?>Step 2: Create index.php
In this step, create a new file index.php. This is the main file used to implement AJAX to search live data and show data into the table.
This screenshot shows the fetched data in table and search field.
index.php
<head>
<title>Registration Form</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://code.jquery.com/jquery-3.7.1.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js"></script>
</head>
<style>
.box
{
max-width:50%;
float: right;
}
</style>
<body>
<div class="container">
<h2 class="text-center">Search Data using Jquery AJAX</h2>
<br/>
<div class="box">
<div class="form-group">
<input type="text" name="search" id="search" placeholder="Search"class="form-control">
</div>
</div>
<table class="table table-bordered table-striped">
<thead style="background-color:#e3a53a;">
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Address</th>
</thead>
<tbody id="result">
</tbody>
</table>
</div>
</body>
</html>Now use jquery AJAX script.
<script type="text/javascript">
$(document).ready(function(){
showdata();
livesearch();
});
function livesearch()
{
$(document).on("keyup","#search", function(){
var search_data = $(this).val();
$.ajax({
url: 'search-data.php',
method: 'post',
data: {search:search_data},
success: function(data)
{
$("#result").html(data);
}
});
});
}
function showdata(){
$.ajax({
url: 'show-data.php',
method: 'post',
success: function(result)
{
$("#result").html(result);
}
});
}
</script>Step 3: Create new file for fetch data
In this step, create a new file show-data.php. This file used to fetch data from database into the table.
show-data.php
<?php
include("connection.php");
$fetch_query = mysqli_query($connection, "select * from tbl_registration");
$row = mysqli_num_rows($fetch_query);
if($row>0)
{
while($res = mysqli_fetch_array($fetch_query))
{?>
<tr>
<td><?php echo $res['id']; ?></td>
<td><?php echo $res['name']; ?></td>
<td><?php echo $res['email']; ?></td>
<td><?php echo $res['phone']; ?></td>
<td><?php echo $res['address']; ?></td>
</tr>
<?php }
}
?>Step 4: Create new file for search data
In this step, create a new file search-data.php. This file used to search data by text field value from the database.
search-data.php
<?php
include('connection.php');
$search = $_POST['search'];
$search_query = mysqli_query($connection, "select * from tbl_registration where name like '%{$search}%'");
$row = mysqli_num_rows($search_query);
if($row>0)
{
while($res = mysqli_fetch_array($search_query))
{?>
<tr>
<td><?php echo $res['id']; ?></td>
<td><?php echo $res['name']; ?></td>
<td><?php echo $res['email']; ?></td>
<td><?php echo $res['phone']; ?></td>
<td><?php echo $res['address']; ?></td>
</tr>
<?php }
}
?>Download Source Code
Join 10,000+ subscriber