Server-side query result filter using checkbox
Server-side query result filter using checkbox
mahmoodrm
Posts: 4Questions: 2Answers: 0
Hello Friends;
I'm trying to filter datatable records using some checkboxes by passing the checkbox value from index.php to fetch.php using ajax. I'm using php server-side processing and oracle database connection. the table is showing records without filter properly but when I want to filter using checkbox, it's not working because the checkbox value is not passing to fetch.php by ajax.
as shown in the snapshot below, the console log is showing that the function to check the checkbox value is working fine when checking and unchecking the checkbox but the value is not sending to fetch.php properly, so the query cannot be executed to filter the records.
I found some comments about custom filtering using the checkbox in the forum like this post, but there is no example that shows me exactly how to do it and I'm entirely lost. it would be appreciated if you please help me to know what is wrong with my code. Thank you in advance
here is the index.php code:
<html>
<head>
<title>FORM</title>
<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css"/>
<link rel="stylesheet" type="text/css" href="css/dataTables.bootstrap.min.css"/>
<meta http-equiv = "Content-Type" content = "text/html; charset=utf-8" />
<style>
INPUT[type=checkbox]:focus{
outline: 1px solid rgba(0, 0, 0, 0.2);
border: 1px solid #c9c2c2;}
INPUT[type=checkbox]{
background-color: #DDD;
border-radius: 2px;
appearance: none;
-webkit-appearance: none;
-moz-appearance: none;
width: 22px;
height: 22px;
cursor: pointer;
position: relative;
top: 5px;}
INPUT[type=checkbox]:checked{
background-color: #7bbe72;
background: #7bbe72 ;}
</style>
</head>
<body>
<div class="container">
<h1 align="center">Alarm</h1>
<br />
<h3></h3>
<div class="container">
<hr>
<div id="region" style="border:1px solid #c0c0c0; text-align:center; padding:10px; height: 50px; float:inherit; font-size:16px; font-weight:bold;">
<input type="checkbox" id="regr1" name="regr1" onclick="CheckboxFunc()"> R1
</div>
<hr>
<div class="panel panel-default">
<div class="panel-body">
<div class="table-responsive">
<table id="emp_list" class="table table-bordered">
<thead>
<tr>
<th>SiteID</th>
<th>OCC Time</th>
<th>Vendor</th>
<th>Region</th>
<th>Province</th>
<th>Number of Depends</th>
<th>Power Alarm Occ Time</th>
<th>Power Alarm Clr Time</th>
<th>Down Site Occ Time</th>
<th>Halted Tech</th>
<th>Halt Time</th>
<th>Active Time</th>
<th>Operator</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
</div>
</div>
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="js/dataTables.bootstrap.min.js"></script>
<script type="text/javascript" src="js/bootstrap.min.js"></script>
<script type="text/javascript" src="js/tabledit.min.js"></script>
<script>
var check01 = 5;
function CheckboxFunc() {
var checkBox = document.getElementById("regr1");
if(checkBox.checked == true){
check01 = 0;
console.log(check01);
$('#emp_list').DataTable().ajax.reload();
} else {
check01 = 5;
console.log(check01);
}
$('#example').DataTable().ajax.reload();
}
</script>
<script type="text/javascript" language="javascript">
$(document).ready(function () {
var dataTable = $("#emp_list").DataTable({
searching: true,
processing: true,
serverSide: true,
paging: true,
order: [],
ajax: {
url: "fetch.php",
type: "POST",
},
});
$("#emp_list").on("draw.dt", function () {
$("#emp_list").Tabledit({
url: "edit.php",
dataType: "json",
columns: {
identifier: [1, "occ_time"],
editable: [
[9, "halted_tech"],
[10, "halt_time"],
[11, "active_time"],
[12, "operator"],
],
},
restoreButton: false,
onSuccess: function (data, textStatus, jqXHR) {
if (data.action == "delete") {
$("#" + data.id).remove();
$("#emp_list").DataTable().ajax.reload();
}
},
});
});
});
</script>
</body>
</html>
and here is the fetch.php:
<?php
//DB Connection
include 'conn.php';
$column = ["siteid","occ_time","vendor","region","province","total_depends","power_alarm_occurance_time","power_alarm_clr_time","down_site_alarm_occurance_time","halted_tech","halt_time","active_time","operator"];
$search_value = $_POST["search"]["value"];
$length = $_POST["length"];
$start = $_POST["start"];
$order = $_POST["order"];
$draw=$_POST['draw'];
##################### Query Builder ####################
$query1 = "SELECT t.siteid,t.occ_time,t.vendor,t.region,t.province,t.total_depends,t.power_alarm_occurance_time,t.power_alarm_clr_time,t.down_site_alarm_occurance_time,t.halted_tech,t.halt_time,t.active_time,t.operator FROM HUBDEPPOWER3_v t ";
$query2 = "where (t.siteid like '%$search_value%' or t.vendor like '%$search_value%' or t.region like '%$search_value%' or t.province like '%$search_value%' or t.province like '%$search_value%' or t.total_depends like '%$search_value%' or t.power_alarm_occurance_time like '%$search_value%' or t.power_alarm_clr_time like '%$search_value%' or t.down_site_alarm_occurance_time like '%$search_value%' or t.halted_tech like '%$search_value%' or t.halt_time like '%$search_value%' or t.active_time like '%$search_value%' or t.operator like '%$search_value%')";
$query4 = "SELECT * FROM HUBDEPPOWER3_v";
#########################################################
$query = $query1;
if ($search_value) {
$query .= $query2;
}
$check=$_GET['check01'];
$q1=" and region ='R1'";
$q2=" and region ='R2'";
if ($check == 0) {
$query .= $query2 . $q1;
} else {
$query .= $query2 . $q2;
}
$query1 = '';
if ($length != -1 && $search_value) {
$query5 = " and rownum between $start and $length";
}
else{
$query5 = " where rownum between $start and $length";
}
$stid3 = oci_parse($connect, $query );
oci_execute($stid3);
$number_filter_row = oci_fetch_all($stid3, $res3);
$data = [];
$stid = oci_parse($connect, $query );
oci_execute($stid);
while(($row = oci_fetch_array($stid))!=false){
$data[] = $row;}
$output = [
'draw' => $draw,
'recordsTotal' => $nrows,
'recordsFiltered' => $number_filter_row,
'data' => $data,
];
echo json_encode($output);
<?php
>
?>
This question has an accepted answers - jump to answer
Answers
An important part of that thread you linked to is how the data is being sent to the server - that isn't happening in your code.
You need to add the checkbox value to the ajax data so your server-side script can then process it, something like this:
Colin
Thank you dear @colin ! I owe you. it's now working like a charm!