Posted By
Bajarangi soft ,
Posted On 23-09-2020
1.Create index.php and implement code as below .
<?php
$con = mysqli_connect("localhost", "root", "", "datatablephp");?>
<!DOCTYPE html>
<html>
<head>
<title>Datatables</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
<br /><br />
<div class="container">
<h3 align="center">How to Create Datatable Using Ajax and PHP</h3>
<br />
<div class="col-md-6" style="display: flex;">
<lable>Gender:</lable>
<select class="form-control" name="multi_search_filter" required>
<option disabled selected value>Select an option</option>
<?php
$sql = 'SELECT DISTINCT gender FROM tbl_employee';
$res = mysqli_query($con, $sql);
if ($res):
while ($row = mysqli_fetch_assoc($res)) {
echo '<option value="'.$row['gender'].'">'.$row['gender'].'</option>';
}
endif; ?>
</select>
</div>
<br />
<br />
</div>
<div class="container">
<div class="table-responsive">
<table id="employee_data" class="table table-striped table-bordered">
<thead>
<tr>
<td>Id</td>
<td>Name</td>
<td>Address</td>
<td>Gender</td>
<td>Designation</td>
<td>Age</td>
</tr>
</thead>
</table>
</div>
</div>
</body>
</html>
you can see above code in that we create database connection to fetch data for dropdown so that we can filter the data from dropdown .
2.Now implement jquery to display datatable as below
<script>
$(document).ready(function(){
$('#employee_data').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'fetch.php',
},
'columns': [
{ data: 'id' },
{ data: 'name' },
{ data: 'address' },
{ data: 'gender' },
{ data: 'designation' },
{ data: 'age' },
]
});
$('select[name="multi_search_filter"]').on('change', function () {
var multi_search_filter = $(this).val();
if (multi_search_filter) {
$.ajax({
type:'POST',
url:'fetch.php',
data:{multi_search_filter:multi_search_filter},
success: function (data) {
console.log(data);
$('#employee_data').html(data);
}
});
} else {
return false;
}
});
});
</script>
above code get data from fetch.php file and it will pass the data for employee_data div
<?php
$con = mysqli_connect("localhost", "root", "", "datatablephp");
if(isset($_POST['draw'])){
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value
// Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery = " and (name like '%".$searchValue."%' or
address like '%".$searchValue."%' or
age like'%".$searchValue."%' ) ";
}
// Total number of records without filtering
$sel = mysqli_query($con,"select count(*) as allcount from tbl_employee");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
// Total number of record with filtering
$empQuery = "select * from tbl_employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$sel = mysqli_query($con,"select count(*) as allcount from tbl_employee WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
// Fetch records
$empQuery = "select * from tbl_employee WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$employeeRec = mysqli_query($con, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($employeeRec)) {
$data[] = array(
"id"=>$row['id'],
"name"=>$row['name'],
"address"=>$row['address'],
"gender"=>$row['gender'],
"designation"=>$row['designation'],
"age"=>$row['age']
);
}
$output = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($output);
}
if(isset($_POST["multi_search_filter"])) {
$multi_search_filter = $_POST['multi_search_filter'];
$output = '';
$query = "SELECT * FROM tbl_employee WHERE gender ='$multi_search_filter'";
$result = mysqli_query($con, $query);
$output .= '
<div class="table-responsive">
<table id="employee_data" class="table table-striped table-bordered">
<tr >
<td>Id</td>
<td>Name</td>
<td>Address</td>
<td>Gender</td>
<td>Designation</td>
<td>Age</td>
</tr>
';
if(mysqli_num_rows($result) > 0)
{
while($row = mysqli_fetch_array($result))
{$output .= '<tr style="background: aliceblue">
<td>'. $row['id'].' </td>
<td>'. $row['name'].' </td>
<td>'. $row['address'].' </td>
<td>'. $row['gender'].' </td>
<td>'. $row['designation'].' </td>
<td>'. $row['age'].' </td>
</tr>';}
}
else
{$output .= '<tr><td colspan="10">Record Not Found</td></tr>';
}
$output .= '</div></table>';
echo $output;
}
?>
<?php
$con = mysqli_connect("localhost", "root", "", "datatablephp");?>
<!DOCTYPE html>
<html>
<head>
<title>Datatables</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
<br /><br />
<div class="container">
<h3 align="center">How to Create Datatable Using Ajax and PHP</h3>
<br />
<div class="col-md-6" style="display: flex;">
<lable>Gender:</lable>
<select class="form-control" name="multi_search_filter" required>
<option disabled selected value>Select an option</option>
<?php
$sql = 'SELECT DISTINCT gender FROM tbl_employee';
$res = mysqli_query($con, $sql);
if ($res):
while ($row = mysqli_fetch_assoc($res)) {
echo '<option value="'.$row['gender'].'">'.$row['gender'].'</option>';
}
endif; ?>
</select>
</div>
<br />
<br />
</div>
<div class="container">
<div class="table-responsive">
<table id="employee_data" class="table table-striped table-bordered">
<thead>
<tr>
<td>Id</td>
<td>Name</td>
<td>Address</td>
<td>Gender</td>
<td>Designation</td>
<td>Age</td>
</tr>
</thead>
</table>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#employee_data').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'fetch.php',
},
'columns': [
{ data: 'id' },
{ data: 'name' },
{ data: 'address' },
{ data: 'gender' },
{ data: 'designation' },
{ data: 'age' },
]
});
$('select[name="multi_search_filter"]').on('change', function () {
var multi_search_filter = $(this).val();
if (multi_search_filter) {
$.ajax({
type:'POST',
url:'fetch.php',
data:{multi_search_filter:multi_search_filter},
success: function (data) {
console.log(data);
$('#employee_data').html(data);
}
});
} else {
return false;
}
});
});
</script>