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:900px; height:500px;">
</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:900px; height:500px;">
</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>

Next Tutorial

Line Charts from SQL Data

Learn how to display trends and changes over time using a line chart.

Back to top