Pie Charts from SQL Data
In this tutorial, the chart data will come directly from a MySQL database.
The example below uses the cyber_security_events table.
Step 1 - Create the SQL Query
The following query counts how many events belong to each risk level.
SELECT
risk_level,
COUNT(*) AS total
FROM cyber_security_events
GROUP BY risk_level;
Example result:
| risk_level | total |
|---|---|
| Low | 15 |
| Medium | 8 |
| High | 3 |
Step 2 - Retrieve the Data with PHP
<?php
$sql = "
SELECT
risk_level,
COUNT(*) AS total
FROM cyber_security_events
GROUP BY risk_level
";
$result = $pdo->query($sql);
$data = [];
while($row = $result->fetch())
{
$data[] = [
$row['risk_level'],
(int)$row['total']
];
}
?>
Step 3 - Convert the PHP Array to JSON
Google Charts uses JavaScript arrays.
PHP can convert data into JavaScript using:
<?php
echo json_encode($data);
?>
Step 4 - Create the Chart
<script>
google.charts.load(
'current',
{'packages':['corechart']}
);
google.charts.setOnLoadCallback(drawChart);
function drawChart()
{
var data = google.visualization.arrayToDataTable([
['Risk Level','Count'],
...<?php echo json_encode($data); ?>
]);
var options = {
title:
'Security Events by Risk Level'
};
var chart =
new google.visualization.PieChart(
document.getElementById('chart_div')
);
chart.draw(data, options);
}
</script>
Step 5 - Add the Chart Container
<div id="chart_div"
style="width:100%; max-width:900px; min-height:320px;">
</div>
Complete Example
<?php
$sql = "
SELECT
risk_level,
COUNT(*) AS total
FROM cyber_security_events
GROUP BY risk_level
";
$result = $pdo->query($sql);
$data = [];
while($row = $result->fetch())
{
$data[] = [
$row['risk_level'],
(int)$row['total']
];
}
?>
<!DOCTYPE html>
<html>
<head>
<script src="https://www.gstatic.com/charts/loader.js"></script>
</head>
<body>
<div id="chart_div"
style="width:100%; max-width:900px; min-height:320px;">
</div>
<script>
google.charts.load(
'current',
{'packages':['corechart']}
);
google.charts.setOnLoadCallback(drawChart);
function drawChart()
{
var data =
google.visualization.arrayToDataTable([
['Risk Level','Count'],
...<?php echo json_encode($data); ?>
]);
var options = {
title:
'Security Events by Risk Level'
};
var chart =
new google.visualization.PieChart(
document.getElementById('chart_div')
);
chart.draw(data, options);
}
</script>
</body>
</html>
Accessibility and data note
Provide the values in an HTML table or concise text summary as well as the chart. Do not rely on colour alone, and give the chart container an accessible name where practical. Google Charts loads JavaScript from Google; use fictional or non-personal summary data only.