Skip to main content

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.

Back to top