The following code is one of a few similar snippets that generate a graph using CanvasJS, with the datapoints for the graph pushed to an array in PHP during a while loop. The code pulls the the date and the escalation event toals for every weekday for the last 30days, does some math on them, and then pushes the calcualted outcome Y value into an array with the dayID/date as the X value/label. The array is then JSON encoded to be used by CanvasJS.
Currently, this particular page (and hence this graph) takes around 9 seconds to run.
Disclaimer: I am not a PHP developer, and I know very little about what is the correct way to do this. This code is my way of doing it, however I know how. I am fully expecting this to be torn apart, haha.
UPDATE: A little info on what the graph shows, and why there are 2 DB's. The graph shows a percentage figure for each day that is derived from the total number of KPI breachable events in a ticket system, and the number of KPI Breach events that day for tickets in the ticket system. It's fairly high volume, and each day could contain 100-400 events. There are 2 databases because the KPI breaching system was made by me, I track events and the status they are in, and log how long they are in that status for, if that time goes above a defined limit, it is classed as breached (or escalated in this context). I store all of that KPI breach tracking information in a different database, as I cannot add tables or otherwise manipulate the database that contains the ticket information/data (it's an ISMS controlled system, and is a managed/supported solution form a 3rd party, I cant change their DB structure.).
So to summarise that, $db is the ticket system, whcih pulls the total number of escalatable events for a day (and the date), and $db2 is my own DB containing escalated events, timestamps and whether or not they've recovered from escalation status/KPI breach.
<div id="chartContainer" style="height: 370px; width: 800px;"></div>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>
<?php
//Debug Mode
//
$debug=0;
if($debug == 1) {
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
}
//Get the creds and conn strings for the 2 DBs
//
include("conf/db.php");
include("conf/db2.php");
//Build Graph Data Points
//
$daysmax=31;
$daycounter=0;
$datapoints = array();
while($daysmax > $daycounter) {
if ($debug == 1) {
echo "Day: ".$daycounter." -- ";
}
$sql_get_day_total_events="SELECT COUNT(*),date_sub(curdate(),INTERVAL $daycounter day) as date1 FROM tblticketlog
WHERE DATE(date) = date_sub(curdate(),INTERVAL $daycounter day)
AND dayofweek(date) NOT IN (1,7)
AND ((action = 'New Support Ticket Opened')
OR (action LIKE 'Status changed to Pending%')
OR (action LIKE 'Status changed to In Progress%')
OR (action LIKE 'New Ticket Response made by%'))";
$get_day_total_events_result=mysqli_query($db,$sql_get_day_total_events);
if(mysqli_num_rows($get_day_total_events_result) > 0) {
while($row = $get_day_total_events_result->fetch_assoc()) {
$day_result_date=strtotime($row['date1']);
$day_result_value=$row['COUNT(*)'];
}
}
$sql_get_day_escs="SELECT COUNT(*),date_sub(curdate(), INTERVAL $daycounter day) as date FROM escalations
WHERE DATE(esc_at) = date_sub(curdate(), INTERVAL $daycounter day)
AND dayofweek(esc_at) NOT IN (1,7)
AND escalated = 1";
$get_day_escs_result=mysqli_query($db2,$sql_get_day_escs);
if(mysqli_num_rows($get_day_escs_result) > 0) {
while($row = $get_day_escs_result->fetch_assoc()) {
$day_escs_value=$row['COUNT(*)'];
}
}
$day_slas_met=$day_result_value - $day_escs_value;
$day_kpi_pcnt = round((($day_slas_met / $day_result_value) * 100),0);
if ($debug == 1) {
echo date("d-m-Y",$day_result_date)." - ".$day_slas_met."/".$day_result_value." - ".$day_kpi_pcnt."%<br />";
}
if ($day_result_value > 0) {
$datapoints[] = [
'x' => 30 - $daycounter,
'label' => date("d-m-Y",$day_result_date),
'y' => $day_kpi_pcnt,
];
}
$daycounter=$daycounter + 1;
}
$datapoints_rev = array_reverse($datapoints);
if ($debug == 1) {
print_r($datapoints_rev);
}
?>
<script>
window.onload = function () {
var chart = new CanvasJS.Chart("chartContainer", {
animationEnabled: true,
exportEnabled: true,
theme: "light1",
title:{
text: "SLA Met Last 30 Days (Weekdays, %)"
},
data: [{
type: "line",
//indexLabel: "{y}",
indexLabelFontColor: "#5A5757",
indexLabelPlacement: "outside",
dataPoints: <?php echo json_encode($datapoints_rev, JSON_NUMERIC_CHECK); ?>
}]
});
chart.render();
}
</script>
If it matters, this is the graph it generates:
GROUP BY
date. PS: it would be nice to know what the graph is about and why you need two databases. \$\endgroup\$