Hi, I am looking for similar drop down filter in php but with database search. I want to filter my charts using Year-wise that will display total number of appointments in each skill for that selected year.
Here is my php code where I tried to plot two graphs, one for all years and next for selection by year.
Much thanks for your help please.
<?php
$dataPoints = array();
$dataPoints2018 = array();
//Best practice is to create a separate file for handling connection to database
try{
// Creating a new connection.
// Replace your-hostname, your-db, your-username, your-password according to your database
$link = new \PDO( 'mysql:host=localhost;dbname=project;charset=utf8mb4', //'mysql:host=localhost;dbname=canvasjs_db;charset=utf8mb4',
'root', //'root',
'', //'',
array(
\PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_PERSISTENT => false
)
);
// qurey for chart render for total appointments for all time topic-wise
$handle = $link->prepare("SELECT COUNT(*) as count, a.id_skill, a.ap_meet_date, s.skill FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill and a.ap_status = 'complete' GROUP BY <code>id_skill</code>");
$handle->execute();
$result = $handle->fetchAll(\PDO::FETCH_OBJ);
foreach($result as $row){
array_push($dataPoints, array("label"=> $row->skill, "y"=> $row->count));
}
$link = null;
// OTHER USEFUL QUERIES
// qurey for disticnt years in appointment table
$year = ("SELECT DISTINCT YEAR(ap_meet_date) FROM appointment As YEAR");
// qurey for chart render for total appointments topic-wise in each year
$sesult_by_year = ("SELECT a.id_skill, YEAR(a.ap_meet_date) As YEAR, s.skill,COUNT(*) as count FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill and a.ap_status = 'complete' GROUP BY <code>id_skill</code>, <code>YEAR</code> ORDER BY <code>YEAR</code>");
// example query for 2017 and 2018 that can be used to print directly for 2017 and 2018
$result_2017 = ("SELECT a.id_skill, YEAR(a.ap_meet_date) As YEAR, s.skill,COUNT(*) as count FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill AND a.ap_status = 'complete' AND YEAR(a.ap_meet_date) = '2017' GROUP BY <code>id_skill</code>");
$result_2018 = ("SELECT a.id_skill, YEAR(a.ap_meet_date) As YEAR, s.skill,COUNT(*) as count FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill AND a.ap_status = 'complete' AND YEAR(a.ap_meet_date) = '2018' GROUP BY <code>id_skill</code>");
foreach($result as $row){
array_push($dataPoints2018, array("label"=> $row->skill, "y"=> $row->count));
}
$link = null;
}
catch(\PDOException $ex){
print($ex->getMessage());
}
?>
<!DOCTYPE HTML>
<html>
<head>
<!-- Script to render pie chart for total count in all years -->
<script>
window.onload = function () {
var chart = new CanvasJS.Chart("chartContainer", {
animationEnabled: true,
exportEnabled: true,
theme: "light1", // "light1", "light2", "dark1", "dark2"
title:{
text: "Skills Distribution"
},
data: [{
type: "pie", //change type to bar, line, area, pie, etc
startAngle: 25,
toolTipContent: "<b>{label}</b>: {y}",
// toolTipContent: "<b>{label}</b>: {y}%",
showInLegend: "true",
legendText: "{label}",
indexLabelFontSize: 16,
indexLabelPlacement: "inside",
indexLabel: "{y}",
// indexLabel: "{label} - {y}",
// indexLabel: "{label} - {y}%",
dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
}]
});
chart.render();
}
</script>
</head>
<body>
<!-- Renders pie chart for total count in all years -->
<div id="chartContainer" style="height: 370px; width: 900px;"></div>
<!-- To render pie chart based on Dropdown filter using database -->
<!-- Dropdown filter using database -->
<br/>
<select class="dropdown" id="dd">
<option value="" selected="selected">Select Year</option>
<?php
if (! empty($distinct_years)) {
foreach ($distinct_years as $key => $value) {
echo '<option value="' . $distinct_years[$key]['YEAR'] . '">' . $distinct_years[$key]['YEAR'] . '</option>';
}
}
?>
</select>
<!-- To render pie chart based on Dropdown filter using database -->
<script type="text/javascript">
var dataPoints = [];
var chart = new CanvasJS.Chart("chartContainer2018",
{
animationEnabled: true,
exportEnabled: true,
theme: "light1", // "light1", "light2", "dark1", "dark2"
},
data: [{
showInLegend: true,
type: 'pie',
startAngle: 25,
toolTipContent: "<b>{label}</b>: {y}",
showInLegend: true,
legendText: "{label}",
dataPoints2018: <?php echo json_encode($dataPoints2018, JSON_NUMERIC_CHECK); ?>
// dataPoints: dataPoints
}]
});
$( ".dropdown" ).change(function() {
chart.options.data[0].dataPoints2018 = [];
var e = document.getElementById("dd");
var selected = e.options[e.selectedIndex].value;
YEAR = jsonData[selected];
// dps = jsonData[selected];
for(var i in YEAR) {
var xVal = dps[i].x;
chart.options.data[0].dataPoints2018.push({x: new Date(YEAR), y: YEAR[i].y});
}
chart.render();
});
</script>
<div id="chartContainer2018" style="height: 370px; width: 900px;"></div>
<script src="https://cdn.canvasjs.com/canvasjs.min.js"></script>
</body>
</html>
