Pie Charts from SQL Data
In the previous tutorial, the chart data was entered manually.
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:800px; height:500px;">
</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:800px; height:500px;">
</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>
Next Tutorial
Bar Charts from SQL Data
Learn how to display the same SQL data as a bar chart.