Display Excel Data in HTML Table using JavaScript
In this tutorial, we will explore the concept of Ajax Live Search using PHP and MySql with Jquery. Many websites feature a search bar where users can search for specific content within the site. If you have ever used Facebook or Twitter, you may have noticed the impressive live search functionality that allows you to search for new friends or followers. When you enter a search query on these sites, the results are instantly displayed without the need to refresh the entire page. This seamless functionality is achieved through the use of Ajax and Jquery. By utilizing Jquery's Ajax HTTP DOM function, data is searched on the server side and the results are sent back to the front-end webpage without any page refresh. Implementing this functionality on your own site can greatly enhance its appearance and user experience. To assist you in learning this technique, I have provided the source code along with this post. I am confident that you will find this tutorial helpful in mastering Ajax Live Search.
Features OF this Post:=>
- Discover how to implement Ajax Live Search with PHP and MySql using Jquery in this tutorial. - Most websites have a search bar where users can search for content on the site. - Facebook and Twitter have an incredible live search feature that allows users to search for new friends or followers. - When searching for something on these sites, the results are displayed instantly without the need for a page refresh. - This feature is made possible by using Ajax with Jquery. - Jquery allows us to use the Ajax http dom function, which searches for data on the server side and sends the results back to the front-end webpage without refreshing the page. - Implementing this functionality will give your site an amazing look and feel. - The source code for this tutorial is provided, so you can easily learn how to implement this feature. - With Ajax Live Search, your users will be able to find what they're looking for quickly and easily. - So, why not give it a try and see how it can improve your website's user experience?
Source Code
index.php
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Webslesson Tutorial</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<div class="container">
<br />
<h2 align="center">Ajax Live Data Search using Jquery PHP MySql</h2><br />
<div class="form-group">
<div class="input-group">
<span class="input-group-addon">Search</span>
<input type="text" name="search_text" id="search_text" placeholder="Search by Customer Details" class="form-control" />
</div>
</div>
<br />
<div id="result"></div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
load_data();
function load_data(query)
{
$.ajax({
url:"fetch.php",
method:"POST",
data:{query:query},
success:function(data)
{
$('#result').html(data);
}
});
}
$('#search_text').keyup(function(){
var search = $(this).val();
if(search != '')
{
load_data(search);
}
else
{
load_data();
}
});
});
</script>
Fetch.php
<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$output = '';
if(isset($_POST["query"]))
{
$search = mysqli_real_escape_string($connect, $_POST["query"]);
$query = "
SELECT * FROM tbl_customer
WHERE CustomerName LIKE '%".$search."%'
OR Address LIKE '%".$search."%'
OR City LIKE '%".$search."%'
OR PostalCode LIKE '%".$search."%'
OR Country LIKE '%".$search."%'
";
}
else
{
$query = "
SELECT * FROM tbl_customer ORDER BY CustomerID
";
}
$result = mysqli_query($connect, $query);
if(mysqli_num_rows($result) > 0)
{
$output .= '
<div class="table-responsive">
<table class="table table bordered">
<tr>
<th>Customer Name</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th>
</tr>
';
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>'.$row["CustomerName"].'</td>
<td>'.$row["Address"].'</td>
<td>'.$row["City"].'</td>
<td>'.$row["PostalCode"].'</td>
<td>'.$row["Country"].'</td>
</tr>
';
}
echo $output;
}
else
{
echo 'Data Not Found';
}
?>
Database Used
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_customer`
--
CREATE TABLE IF NOT EXISTS `tbl_customer` (
`CustomerID` int(11) NOT NULL AUTO_INCREMENT,
`CustomerName` varchar(250) NOT NULL,
`Address` text NOT NULL,
`City` varchar(250) NOT NULL,
`PostalCode` varchar(30) NOT NULL,
`Country` varchar(100) NOT NULL,
PRIMARY KEY (`CustomerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
--
-- Dumping data for table `tbl_customer`
--
INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),
(5, 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA'),
(9, 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(11, 'Paul Henriot', '2, rue du Commerce', 'Reims', '51100', 'France'),
(12, 'Helvetius Nagy', '722 DaVinci Blvd.', 'Kirkland', '98034', 'USA'),
(13, 'Karin Josephs', 'Luisenstr. 48', 'Butte', '59801', 'USA');
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Webslesson Tutorial</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<div class="container">
<br />
<h2 align="center">Ajax Live Data Search using Jquery PHP MySql</h2><br />
<div class="form-group">
<div class="input-group">
<span class="input-group-addon">Search</span>
<input type="text" name="search_text" id="search_text" placeholder="Search by Customer Details" class="form-control" />
</div>
</div>
<br />
<div id="result"></div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
load_data();
function load_data(query)
{
$.ajax({
url:"fetch.php",
method:"POST",
data:{query:query},
success:function(data)
{
$('#result').html(data);
}
});
}
$('#search_text').keyup(function(){
var search = $(this).val();
if(search != '')
{
load_data(search);
}
else
{
load_data();
}
});
});
</script>
Fetch.php
<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$output = '';
if(isset($_POST["query"]))
{
$search = mysqli_real_escape_string($connect, $_POST["query"]);
$query = "
SELECT * FROM tbl_customer
WHERE CustomerName LIKE '%".$search."%'
OR Address LIKE '%".$search."%'
OR City LIKE '%".$search."%'
OR PostalCode LIKE '%".$search."%'
OR Country LIKE '%".$search."%'
";
}
else
{
$query = "
SELECT * FROM tbl_customer ORDER BY CustomerID
";
}
$result = mysqli_query($connect, $query);
if(mysqli_num_rows($result) > 0)
{
$output .= '
<div class="table-responsive">
<table class="table table bordered">
<tr>
<th>Customer Name</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th>
</tr>
';
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>'.$row["CustomerName"].'</td>
<td>'.$row["Address"].'</td>
<td>'.$row["City"].'</td>
<td>'.$row["PostalCode"].'</td>
<td>'.$row["Country"].'</td>
</tr>
';
}
echo $output;
}
else
{
echo 'Data Not Found';
}
?>
Database Used
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_customer`
--
CREATE TABLE IF NOT EXISTS `tbl_customer` (
`CustomerID` int(11) NOT NULL AUTO_INCREMENT,
`CustomerName` varchar(250) NOT NULL,
`Address` text NOT NULL,
`City` varchar(250) NOT NULL,
`PostalCode` varchar(30) NOT NULL,
`Country` varchar(100) NOT NULL,
PRIMARY KEY (`CustomerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
--
-- Dumping data for table `tbl_customer`
--
INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),
(5, 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA'),
(9, 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(11, 'Paul Henriot', '2, rue du Commerce', 'Reims', '51100', 'France'),
(12, 'Helvetius Nagy', '722 DaVinci Blvd.', 'Kirkland', '98034', 'USA'),
(13, 'Karin Josephs', 'Luisenstr. 48', 'Butte', '59801', 'USA');
