Skip to main content

Bar Charts from SQL Data

Bar charts are useful for comparing values between categories.

In this tutorial, the number of security events at each risk level will be displayed as a bar chart.

Step 1 - Create the SQL Query

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 - Create the Chart Container

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>

Step 4 - Create the Bar Chart

<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data =

        google.visualization.arrayToDataTable([

        ['Risk Level','Events'],

        ...<?php echo json_encode($data); ?>

    ]);


    var options = {

        title:'Security Events by Risk Level',

        legend:{position:'none'}

    };


    var chart =

        new google.visualization.BarChart(

            document.getElementById('chart_div')

        );


    chart.draw(data, options);

}

</script>

image-1782085906217.png

Changing the Chart Type

Only one line of code needs to change.

Pie Chart:

new google.visualization.PieChart()

Bar Chart:

new google.visualization.BarChart()

Column Chart:

new google.visualization.ColumnChart()

Line Chart:

new google.visualization.LineChart()

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','Events'],

        ...<?php echo json_encode($data); ?>

    ]);


    var options = {

        title:'Security Events by Risk Level',

        legend:{position:'none'}

    };


    var chart =

        new google.visualization.BarChart(

            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