I Have created the script to generate the pie chart based on data from my database. It is displaying the work hours based on department just fine. However, I am unable to display by filtering between dates. Can I know what or how the code needs to be fixed to filter. In database I have already created timestamp named “CreatedOn”.
<?php
try {
$query = $connection->prepare("SELECT Department, SUM(workHrs) as number FROM serviceapplication GROUP BY Department");
$query->execute();
$query->setFetchMode(PDO::FETCH_ASSOC);
$data = $query->fetchAll();
// var_dump($data);
} catch (PDOException $e) {
echo $e->getMessage();
}
?>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['Department', 'Work Hours'],
<?php
foreach($data as $data) :
echo "['".$data["Department"]."', ".$data["number"]."],";
endforeach;
?>
]);
var options = {
backgroundColor: "none",
title: '',
width: 900,
height: 500,
pieHole: 0.5,
colors: ['#4c325c', '#8ea5cc', '#aa579f', '#391d9d', '#fcbd9c', '#dc346c'],
};
var chart = new google.visualization.PieChart(document.getElementById('piechart'));
chart.draw(data, options);
}
</script>
<form action="" method="GET">
<label class="text-secondary">From Date</label>
<input type="date" name="from_date2" value="<?php if(isset($_GET['from_date2'])){ echo $_GET['from_date2']; } ?>" class="form-control">
<label class="text-secondary">To Date</label>
<input type="date" name="to_date2" value="<?php if(isset($_GET['to_date2'])){ echo $_GET['to_date2']; } ?>" class="form-control">
<label class="text-secondary">Filter</label> <br>
<button type="submit" class="btn btn-primary">Filter</button>
</form>
<br>
<?php
if(isset($_GET['from_date2']) && isset($_GET['to_date2']))
{
$from_date2 = $_GET['from_date2'];
$to_date2 = $_GET['to_date2'];
try {
$query = $connection->prepare("SELECT * FROM serviceapplication WHERE CreatedOn BETWEEN '$from_date2' AND '$to_date2' ");
$query->execute();
$query->setFetchMode(PDO::FETCH_ASSOC);
$data = $query->fetchAll();
$count = 0;
// var_dump($data);
} catch (PDOException $e) {
echo $e->getMessage();
}
foreach($data as $data) :
?>
<div id="piechart"></div>
<?php
endforeach;
}
?>Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
- Modified Code , you need to set $data array accordingly.
-
FIlter – group by column and required fields (currently i grouped by dept)
try { if (isset($_GET['from_date2']) && isset($_GET['to_date2'])) { $from_date2 = $_GET['from_date2']; $to_date2 = $_GET['to_date2']; //SQL injection you can handle $query = $connection->prepare("SELECT Department, SUM(workHrs) as number FROM serviceapplication WHERE CreatedOn BETWEEN ? AND ? GROUP BY Department"); //$query->bind_param("ss", $from_date2, $to_date2); $query->execute(array($from_date2, $to_date2)); //$query->execute(); $query->setFetchMode(PDO::FETCH_ASSOC); $data = $query->fetchAll(); $count = 0; echo "data goes here = date filter ";print_r($data); } else { $query = $connection->prepare("SELECT Department, SUM(workHrs) as number FROM serviceapplication GROUP BY Department"); $query->execute(); $query->setFetchMode(PDO::FETCH_ASSOC); $data = $query->fetchAll(); echo "data goes here";print_r($data); } } catch (PDOException $e) { echo $e->getMessage(); } ?> <head> <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script> <script type="text/javascript"> google.charts.load('current', {'packages':['corechart']}); google.charts.setOnLoadCallback(drawChart); function drawChart() { var data = google.visualization.arrayToDataTable([ ['Department', 'Work Hours'], <?php foreach($data as $data) : echo "['".$data["Department"]."', ".$data["number"]."],"; endforeach; ?> ]); var options = { backgroundColor: "none", title: '', width: 900, height: 500, pieHole: 0.5, colors: ['#4c325c', '#8ea5cc', '#aa579f', '#391d9d', '#fcbd9c', '#dc346c'], }; var chart = new google.visualization.PieChart(document.getElementById('piechart')); chart.draw(data, options); } </script> <form action="" method="GET"> <label class="text-secondary">From Date</label> <input type="date" name="from_date2" value="<?php if(isset($_GET['from_date2'])){ echo $_GET['from_date2']; } ?>" class="form-control"> <label class="text-secondary">To Date</label> <input type="date" name="to_date2" value="<?php if(isset($_GET['to_date2'])){ echo $_GET['to_date2']; } ?>" class="form-control"> <label class="text-secondary">Filter</label> <br> <button type="submit" class="btn btn-primary">Filter</button> </form> <br> <?php print_r($data); foreach($data as $dat) : ?> <div id="piechart"></div> <?php endforeach; ?>
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0