I will like to filter a table that has is data fetch from database when checkbox is checked, this is not working.
below is the php file
<?php
$pdo = new PDO(
'mysql:host=localhost;dbname=db-dashboard', 'root', ''
);
$select = 'SELECT full_name, phone_number, gender, age, qualification, state, lga, community, crop, farm_size, created';
$from = ' FROM farmers';
$where = ' WHERE TRUE';
$opts = isset($_POST['filterOpts'])?
$_POST['filterOpts'] :
array('');
if (in_array("crop", $opts)){
$where .= " AND crop = 'crop'";
}
$sql = $select . $from . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>
<div class="row">
<div class="col-md-12">
<div class="tile">
<div class="tile-body">
<table class="table table-hover table-responsive table-bordered" id="sampleTable">
<div class="row-center">
<?php
if(isset($_SESSION['error'])){
echo
"
<div class='alert alert-danger text-center'>
<button class='close'></button>
".$_SESSION['error']."
</div>
";
unset($_SESSION['error']);
}
if(isset($_SESSION['success'])){
echo
"
<div class='alert alert-success text-center'>
<button class='close'></button>
".$_SESSION['success']."
</div>
";
unset($_SESSION['success']);
}
?>
</div>
<form>
<div class="container">
<!-- <p>The form below contains three inline checkboxes:</p> -->
<h6>Crops: </h6>
<?php
$query = "
SELECT DISTINCT(crop) FROM farmers ORDER BY crop DESC
";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{
?>
<label class="checkbox-inline">
<input type="checkbox" class="filter_all crop" value="<?php echo $row['crop']; ?>">
<?php echo $row['crop']; ?>
</label>
<?php
}
?>
</form>
</div>
<thead>
<tr>
<!-- <th>#</th> -->
<th>FullNname</th>
<th>PhoneNumber</th>
<th>Gender</th>
<th>Age</th>
<th>Qualification</th>
<th>State</th>
<th>LGA</th>
<th>Community</th>
<th>Crop</th>
<th>FarmSize(Ha)</th>
<th>Created</th>
<!-- <th width="150">Action</th> -->
</tr>
</thead>
<tbody>
<?php
include('recordsEditDeleteModal.php');
include('addRecordsModal.php') ;
?>
</tbody>
</table>
</div>
</div>
</div>
</div><br><br><br>
</main>
<?php include("includes/footer.php"); ?>
<!-- checkbox filter -->
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
function makeTable(data){
var tbl_body = "";
$.each(data, function() {
var tbl_row = "";
$.each(this, function(k , v) {
tbl_row += "<td>"+v+"</td>";
})
tbl_body += "<tr>"+tbl_row+"</tr>";
})
return tbl_body;
}
function getEmployeeFilterOptions(){
var opts = [];
$checkboxes.each(function(){
if(this.checked){
opts.push(this.name);
}
});
return opts;
}
function updateEmployees(opts){
$.ajax({
type: "POST",
url: "submit.php",
dataType : 'json',
cache: false,
data: {filterOpts: opts},
success: function(records){
$('#sampleTable tbody').html(makeTable(records));
}
});
}
var $checkboxes = $("input:checkbox");
$checkboxes.on("change", function(){
var opts = getEmployeeFilterOptions();
updateEmployees(opts);
});
updateEmployees();
</script>
Here is the HTML with ajax code