Skip to main content

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:800px;100%; max-width:900px; min-height:500px;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:800px;100%; max-width:900px; min-height:500px;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.

Back to top