Using MySQL data for a bar chart

scuderia · 25-04-2016 9:31pm #1

Hi

Im currently trying to create a bar chart using FusionCharts, PHP and MySQL.

Basically, I have a table called "incidents" and in it I have the headings "ID" which auto increments and "Severity" which contains either low, medium, high or urgent.

So what I've done is fetched data from that table and used FusionTables to display it. That all works fine. However, on my bar chart on the Y axis I have the data from the column "id" and on the Y axis data from the column "severity".

How can I have 4 bars labelled low, medium, high and urgent on the X axis of the bar chart and on the Y axis have the amount of each of those. So if there are 35 HIGH incidents in my database that bar should go to 37 on the Y axis if you know what I mean.

At the moment, every time I add data to that table, an additional bar appears on the x axis and it's id on the y axis.

Is this just something to do with the SQL query or ....?

Any help appreciated. Below is my code:

<?php
include("fusioncharts.php");

$hostdb = "localhost"; // MySQl host
$userdb = "root"; // MySQL username
$passdb = ""; // MySQL password
$namedb = "fyp"; // MySQL database name

$dbhandle = new mysqli($hostdb, $userdb, $passdb, $namedb);

if ($dbhandle->connect_error) {
exit("There was an error with your connection: ".$dbhandle->connect_error);
}

?>

<html>
<head>
<title>Test</title>
<script src="fusioncharts.js"></script>
</head>

<body>

<?php

$strQuery = "SELECT DISTINCT severity, id FROM incidents; ";

$result = $dbhandle->query($strQuery) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");

if ($result) {

$arrData = array(
"chart" => array(
"caption"=> "Current Incidents",
"subCaption"=> "",
"captionFontColor"=> "#fff",
"captionFontBold"=> "0",
"captionFontSize"=> "20",

// x and y axes configuration options
"xAxisName"=> "Severity",
"xAxisNameFontSize"=> "18",
"xAxisNameFontBold"=> "0",

"yAxisName"=> "ID",
"yAxisNameFontSize"=> "18",
"yAxisNameFontBold"=> "0",

// general chart configuration options
"baseFont"=> "Open Sans",
"paletteColors"=> "#06A69E",
"plotFillAlpha"=> "90",
"usePlotGradientColor"=> "0",
"numberPrefix"=> "",
"bgcolor"=> "#17153F",
"bgalpha"=> "95",
"canvasbgalpha"=> "0",
"basefontcolor"=> "#F7F3E7",
"showAlternateHGridColor"=> "0",
"divlinealpha"=> "50",
"divlinedashed"=> "0",
"rotateyaxisname"=> "1",
"canvasbordercolor"=> "#ffffff",
"canvasborderthickness"=> ".3",
"canvasborderalpha"=> "100",
"showValues"=> "0",
"plotSpacePercent"=> "8",
"labelFontSize"=> "15",
"outCnvBaseFontSize"=> "13",
"showLimits"=> "0",

// tooltip configuration options
"toolTipBgColor"=> "#000",
"toolTipPadding"=> "12",
"toolTipBorderRadius"=> "3",
"toolTipBorderThickness"=> "1",
"toolTipBorderColor"=> "#ccc",
"toolTipBgAlpha"=> "70"
)
);

$arrData["data"] = array();

while($row = mysqli_fetch_array($result)) {
array_push($arrData["data"], array(
"label" => $row["severity"],
"value" => $row["id"]
)
);
}

$jsonEncodedData = json_encode($arrData);

$columnChart = new FusionCharts("column3d", "" , "50%", "400", "column-chart", "json", $jsonEncodedData);
$columnChart->render();

$dbhandle->close();

}

?>
<center>
<div id="column-chart">Awesome Chart on its way!</div></center>
</body>
</html>

Buford T Justice · 25-04-2016 10:57pm

I'm not familiar with fusionCharts, but for a summary report such as this, shouldn't you be looking to aggregate your data?

Talisman · 26-04-2016 9:55am

You want to count the number of incidents of each severity level:

SELECT severity, COUNT(id) AS numIncidents
FROM incidents
GROUP BY severity;

Using MySQL data for a bar chart

Comments