Searching of table data is very required feature in any web application, so here we have discuss something like that. In this post we have learn how to search or filter table data by autocomplete textbox using bootstrap typeahead plugin with PHP script with Ajax Jquery. We have already discuss topic on search html data by using Ajax with Jquery and we have also discuss how to use Bootstrap Typeahead plugin for make autocomplete textbox with Ajax PHP. But now here we have seen combination of both that means we have we have learn how to integrate Bootstrap Typeahead plugin for search table data on server side PHP script with Ajax.
Bootstrap Typeahead plugin is used to make Autocomplete that means we have start to type in textbox then it drop down posible data according to what we have type in textbox. In short this plugin add autocomplete feature to any input type textbox to drop down hint to user while he start filling data in textbox. So here we have used this plugin for search table data with autocomplete textbox.
For make autocomplete search box for search HTML data by send Ajax request to PHP script. In this feature user can search any table column data. Here first we want to make autocomplete search textbox so for this we have use Bootstrap Typeahead plugin by using typeahead() method, by using this plugin we can make auto completed textbox like Google or any other social media site. Then after we have want to search table data according to hint given by this plugin, so here we have integrate search query in typeahead() method, so when this plugin return possible data from autocomplete textbox then it also display search or filter data on table also. This way we have use Bootstrap Typeahead plugin for search table data by using autocomplete input text.
Source Code
index.php
<!DOCTYPE html>
<html>
<head>
<title>How to Search Table Data by Typehead with PHP Ajax</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-3-typeahead/4.0.2/bootstrap3-typeahead.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
</head>
<body>
<br /><br />
<div class="container">
<h2 align="center">How to Search Table Data by Typehead with PHP Ajax</h2>
<br /><br />
<label>Search Employee Details</label>
<div id="search_area">
<input type="text" name="employee_search" id="employee_search" class="form-control input-lg" autocomplete="off" placeholder="Type Employee Name" />
</div>
<br />
<br />
<div id="employee_data"></div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
load_data('');
function load_data(query, typehead_search = 'yes')
{
$.ajax({
url:"fetch.php",
method:"POST",
data:{query:query, typehead_search:typehead_search},
success:function(data)
{
$('#employee_data').html(data);
}
});
}
$('#employee_search').typeahead({
source: function(query, result){
$.ajax({
url:"fetch.php",
method:"POST",
data:{query:query},
dataType:"json",
success:function(data){
result($.map(data, function(item){
return item;
}));
load_data(query, 'yes');
}
});
}
});
$(document).on('click', 'li', function(){
var query = $(this).text();
load_data(query);
});
});
</script>
fetch.php
<?php
//fetch.php
if(isset($_POST["query"]))
{
$connect = mysqli_connect("localhost", "root", "", "testing");
$request = mysqli_real_escape_string($connect, $_POST["query"]);
$query = "
SELECT * FROM tbl_employee
WHERE name LIKE '%".$request."%'
OR gender LIKE '%".$request."%'
OR designation LIKE '%".$request."%'
";
$result = mysqli_query($connect, $query);
$data =array();
$html = '';
$html .= '
<table class="table table-bordered table-striped">
<tr>
<th>Name</th>
<th>Gender</th>
<th>Designation</th>
</tr>
';
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_array($result))
{
$data[] = $row["name"];
$data[] = $row["gender"];
$data[] = $row["designation"];
$html .= '
<tr>
<td>'.$row["name"].'</td>
<td>'.$row["gender"].'</td>
<td>'.$row["designation"].'</td>
</tr>
';
}
}
else
{
$data = 'No Data Found';
$html .= '
<tr>
<td colspan="3">No Data Found</td>
</tr>
';
}
$html .= '</table>';
if(isset($_POST['typehead_search']))
{
echo $html;
}
else
{
$data = array_unique($data);
echo json_encode($data);
}
}
?>
Database
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_employee`
--
CREATE TABLE IF NOT EXISTS `tbl_employee` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`gender` varchar(10) NOT NULL,
`designation` varchar(30) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `tbl_employee`
--
INSERT INTO `tbl_employee` (`id`, `name`, `gender`, `designation`) VALUES
(1, 'Micheal Bruce', 'Male', 'System Architect'),
(5, 'Clara Gilliam', 'Female', 'Programmer'),
(6, 'Robert L. Hoskins', 'Male', 'Personal secretary'),
(7, 'Walter M. Watkins', 'Male', 'Crane and tower operator'),
(8, 'Stanley L. Gomez', 'Male', 'HVACR technician'),
(9, 'Erik C. Parker', 'Male', 'Graduate teaching assistant'),
(11, 'Stephanie B. Boland', 'Female', 'Adjuster'),
(12, 'Donald P. Fitzgerald', 'Male', 'Pharmacy aide'),
(13, 'Angel Lewis', 'Female', 'Paper goods tender'),
(14, 'Justin Dean', 'Male', 'Magnetic resonance Technolist'),
(15, 'Nora Blake', 'Female', 'Neuroscience nurse'),
(16, 'Russell Fox', 'Male', 'Safe repairer'),
(17, 'Daryl Bradley', 'Female', 'Intructional coordinator'),
(18, 'Benjamin Gonzales', 'Male', 'Musician'),
(19, 'Viola Francis', 'Female', 'Amusement machine servicer'),
(20, 'Reginald Benson', 'Male', 'Management information systems'),
(21, 'Glenda Ray', 'Female', 'Business support assistant'),
(22, 'Paula Vargas', 'Female', 'Electrical engineer'),
(23, 'Mark Armstrong', 'Male', 'Merchandise manager'),
(24, 'Jaime Campbell', 'Female', 'Petroleum pump system operator'),
(25, 'Mike Beck', 'Male', 'Placement officer'),
(26, 'Ann Lowe', 'Female', 'Computer scientist');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_employee`
--
ALTER TABLE `tbl_employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=27;
