Data Visualisation with the Google Chart, PHP and MySQL

Pie Chart Google chart provides a perfect way to visualise and report data on your website. It includes powerful, simple to use, and free interactive charts and data tools. You can easily connect them to your database and make real time charts and graphs. For a quick start, Google has provided an extensive documentation named Hello Charts!. However, for those of you who own a website running on PHP and MySQL I suggest you to read this step by step tutorial. So let’s get started.

Dummy data generation

For the sake of demo purposes, I’ve used Mockaroo to generate some dummy data. You can either import this SQL file to your database or generate new random data using the same schema here. As you see, the first five rows are as below:

id	first_name	last_name	country	age	yearly_salary	total_credits
1	Nicole	Simmons	Israel	59	79782	7762.30
2	Virginia	Jacobs	Argentina	84	52226	21379.73
3	Michael	Anderson	Puerto Rico	81	86333	39871.82
4	Philip	Arnold	Rwanda	67	88786	55603.28
5	Teresa	Fields	Korea, North	57	58692	34026.17

Now we’ve got the data and can create some charts based on that. Let’s first try to show the percent of people within age range in a pie chart.

The percent of people within age range in a pie chart

To display the percent of people within age range in a pie chart first we need to extract the relevant data from database. Then we should populate Google Chart DataTable in a function and call the function. We also need to have a container (DIV) to hold the function output.

Preparing the data

In order to extract the relevant data you need to run this query against your database:

SELECT
 CASE
  WHEN age >= 16 AND age <= 20 THEN '16-20'
  WHEN age >= 21 AND age <= 30 THEN '21-30'
  WHEN age >= 31 AND age <= 40 THEN '31-40'
  WHEN age >= 41 AND age <= 50 THEN '41-50'
  WHEN age >= 51 AND age <= 60 THEN '51-60'
  WHEN age >= 61 AND age <= 70 THEN '61-70'
  WHEN age >= 71 AND age <= 80 THEN '71-80'
  WHEN age >= 81 AND age <= 90 THEN '81-90'
  END AS agerange, COUNT(id) AS Number
FROM dummy_data GROUP BY agerange;

SELECT

CASE

WHEN age >= 16 AND age <= 20 THEN '16-20'

WHEN age >= 21 AND age <= 30 THEN '21-30'

WHEN age >= 31 AND age <= 40 THEN '31-40'

WHEN age >= 41 AND age <= 50 THEN '41-50'

WHEN age >= 51 AND age <= 60 THEN '51-60'

WHEN age >= 61 AND age <= 70 THEN '61-70'

WHEN age >= 71 AND age <= 80 THEN '71-80'

WHEN age >= 81 AND age <= 90 THEN '81-90'

END AS agerange, COUNT(id) AS Number

FROM dummy_data GROUP BY agerange;

To make sure that your query has no error, print the result using print_r($results) and you should get the same output as below:

Array
(
    [0] => Array
        (
            [agerange] => 16-20
            [Number] => 27
        )
    [1] => Array
        (
            [agerange] => 21-30
            [Number] => 71
        )
    [2] => Array
        (
            [agerange] => 31-40
            [Number] => 66
        )
    [3] => Array
        (
            [agerange] => 41-50
            [Number] => 66
        )
    [4] => Array
        (
            [agerange] => 51-60
            [Number] => 75
        )
    [5] => Array
        (
            [agerange] => 61-70
            [Number] => 60
        )
    [6] => Array
        (
            [agerange] => 71-80
            [Number] => 61
        )
    [7] => Array
        (
            [agerange] => 81-90
            [Number] => 74
        )
)

Array

(

[0] => Array

(

[agerange] => 16-20

[Number] => 27

)

[1] => Array

(

[agerange] => 21-30

[Number] => 71

)

[2] => Array

(

[agerange] => 31-40

[Number] => 66

)

[3] => Array

(

[agerange] => 41-50

[Number] => 66

)

[4] => Array

(

[agerange] => 51-60

[Number] => 75

)

[5] => Array

(

[agerange] => 61-70

[Number] => 60

)

[6] => Array

(

[agerange] => 71-80

[Number] => 61

)

[7] => Array

(

[agerange] => 81-90

[Number] => 74

)

Populating DataTable

But how should we populate a pie chart using this data? Well, if you have a look at the first example here, you will see there is a data table feeding the chart:

var data = new google.visualization.DataTable();
 data.addColumn('string', 'Topping');
 data.addColumn('number', 'Slices');
 data.addRows([
  ['Mushrooms', 3],
  ['Onions', 1],
  ['Olives', 1],
  ['Zucchini', 1],
  ['Pepperoni', 2]
]);

var data = new google.visualization.DataTable();

data.addColumn('string', 'Topping');

data.addColumn('number', 'Slices');

data.addRows([

['Mushrooms', 3],

['Onions', 1],

['Olives', 1],

['Zucchini', 1],

['Pepperoni', 2]

]);

So first we need to add columns that in our case are Age Range and Number. The next step is to create an array of row objects for addRows method. To achieve this I used a foreach loop as it’s demonstrated below:

$pie_chart_data = array();
foreach($results as $result)
{
 $pie_chart_data[] = array($result['agerange'], (int)$result['Number']);
}
$pie_chart_data = json_encode($pie_chart_data);

$pie_chart_data = array();

foreach($results as $result)

{

$pie_chart_data[] = array($result['agerange'], (int)$result['Number']);

}

$pie_chart_data = json_encode($pie_chart_data);

Note that data type for the second column is integer, that’s why I cast $result['Number'] from string to integer. Again if you echo($pie_chart_data); you should see the same output:

[["16-20",27],["21-30",71],["31-40",66],["41-50",66],["51-60",75],
["61-70",60],["71-80",61],["81-90",74]]

1 2	[["16-20",27],["21-30",71],["31-40",66],["41-50",66],["51-60",75], ["61-70",60],["71-80",61],["81-90",74]]

Now DataTable can be populated by passing the data using addRows() method:

var data = new google.visualization.DataTable();
        data.addColumn('string', 'Age Range');
        data.addColumn('number', 'Number');
        data.addRows({$pie_chart_data});

var data = new google.visualization.DataTable();

data.addColumn('string', 'Age Range');

data.addColumn('number', 'Number');

data.addRows({$pie_chart_data});

Loading necessary libraries

Basically, as it’s stated here, to display a chart your page needs to load at least three libraries (Google AJAX API, Google Visualization library and Individual chart libraries). For a pie chart, corechart package can be used as the individual chart library. So we need to include this in our code:

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
   google.load('visualization', '1.0', {'packages':['corechart']});
</script>

google.load('visualization', '1.0', {'packages':['corechart']});

</script>

Putting them all together

Here is the final code:

<?php

$HTML =<<<XYZ
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">

      // Load the Visualization API and the charts package.
      google.load('visualization', '1.0', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {

        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Age Range');
        data.addColumn('number', 'Number');
        data.addRows({$pie_chart_data});

        // Set chart options
        var options = {title:'The Percent Of People Within Age Range',
         titleTextStyle: {fontName: 'Lato', fontSize: 18, bold: true},
                       height: 400,
                       is3D: true,
         colors:['#0F4F8D','#2B85C1','#8DA9BF','#F2C38D','#E6AC03','#F09B35', '#D94308', '#013453'],
         chartArea:{left:30,top:30,width:'100%',height:'80%'}};

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.PieChart(document.getElementById('pie_chart_div'));
        chart.draw(data, options);
      }

      // Make the charts responsive
      jQuery(document).ready(function(){
        jQuery(window).resize(function(){
          drawChart();
        });
      });

    </script>

    <div id="pie_chart_div"></div>
XYZ;

    echo $HTML;

?>

<?php

$HTML =<<<XYZ

// Load the Visualization API and the charts package.

google.load('visualization', '1.0', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.

google.setOnLoadCallback(drawChart);

// Callback that creates and populates a data table,

// instantiates the pie chart, passes in the data and

// draws it.

function drawChart() {

// Create the data table.

var data = new google.visualization.DataTable();

data.addColumn('string', 'Age Range');

data.addColumn('number', 'Number');

data.addRows({$pie_chart_data});

// Set chart options

var options = {title:'The Percent Of People Within Age Range',

titleTextStyle: {fontName: 'Lato', fontSize: 18, bold: true},

height: 400,

is3D: true,

colors:['#0F4F8D','#2B85C1','#8DA9BF','#F2C38D','#E6AC03','#F09B35', '#D94308', '#013453'],

chartArea:{left:30,top:30,width:'100%',height:'80%'}};

// Instantiate and draw our chart, passing in some options.

var chart = new google.visualization.PieChart(document.getElementById('pie_chart_div'));

chart.draw(data, options);

}

// Make the charts responsive

jQuery(document).ready(function(){

jQuery(window).resize(function(){

drawChart();

});

</script>

XYZ;

echo $HTML;

As you see, I’ve used Heredoc (XYZ) for string encapsulation. In the first lines of our code, all of the necessary libraries are loaded including corechart package. In the line 12 you see google.setOnLoadCallback(drawChart); that makes sure the document is fully loaded before calling drawChart function. In drawChart function, other than populating DataTable some options for the pie chart are specified. You can have a look at pie chart page to find all the possible options. As you see, I haven’t defined width for the chart because I wanted to make it responsive. The other configuration is to specify the ID of the DIV that holds the function output. Also don’t forget to put the DIV itself somewhere in your page. The last thing is to call the drawChart() function once the size of page is changed. In this way, your chart is displayed properly in mobile devices.

Total Credits And Salary Averages Within Age Range in a Column Chart

Now you can easily use what you’ve learned for other chart types. So there is no need to explain the rest of this tutorial in detail.

Preparing the data

Use this query:

SELECT
 CASE
  WHEN age >= 16 AND age <= 20 THEN '16-20'
  WHEN age >= 21 AND age <= 30 THEN '21-30'
  WHEN age >= 31 AND age <= 40 THEN '31-40'
  WHEN age >= 41 AND age <= 50 THEN '41-50'
  WHEN age >= 51 AND age <= 60 THEN '51-60'
  WHEN age >= 61 AND age <= 70 THEN '61-70'
  WHEN age >= 71 AND age <= 80 THEN '71-80'
  WHEN age >= 81 AND age <= 90 THEN '81-90'
  END AS agerange, AVG(yearly_salary) AS salary_average, AVG(total_credits) AS credits_average
FROM dummy_data GROUP BY agerange;

SELECT

CASE

WHEN age >= 16 AND age <= 20 THEN '16-20'

WHEN age >= 21 AND age <= 30 THEN '21-30'

WHEN age >= 31 AND age <= 40 THEN '31-40'

WHEN age >= 41 AND age <= 50 THEN '41-50'

WHEN age >= 51 AND age <= 60 THEN '51-60'

WHEN age >= 61 AND age <= 70 THEN '61-70'

WHEN age >= 71 AND age <= 80 THEN '71-80'

WHEN age >= 81 AND age <= 90 THEN '81-90'

END AS agerange, AVG(yearly_salary) AS salary_average, AVG(total_credits) AS credits_average

FROM dummy_data GROUP BY agerange;

Populating DataTable

In order to populate DataTable there is a simpler and readable function named arrayToDataTable(). Using this function you need to add columns as the first element of the array:

$column_chart_data = array(array("Age Range", "Salary Average", "Total Credits Average"));
foreach($results as $result)
 {
  $column_chart_data[] = array($result['agerange'], round($result['salary_average'],2), round($result['credits_average'],2));
 }
$column_chart_data = json_encode($column_chart_data);

$column_chart_data = array(array("Age Range", "Salary Average", "Total Credits Average"));

foreach($results as $result)

{

$column_chart_data[] = array($result['agerange'], round($result['salary_average'],2), round($result['credits_average'],2));

}

$column_chart_data = json_encode($column_chart_data);

Here is the output of echo ($column_chart_data);

[["Age Range","Salary Average","Total Credits Average"],["16-20",67531.96,27190.76],["21-30",67826.45,27519.29],["31-40",64797.41,26518.38],["41-50",67120.44,30169.27],["51-60",70980.97,32099.15],["61-70",69606.1,28444.99],["71-80",66535.97,28491.04],["81-90",66519.99,26383.24]]

1	[["Age Range","Salary Average","Total Credits Average"],["16-20",67531.96,27190.76],["21-30",67826.45,27519.29],["31-40",64797.41,26518.38],["41-50",67120.44,30169.27],["51-60",70980.97,32099.15],["61-70",69606.1,28444.99],["71-80",66535.97,28491.04],["81-90",66519.99,26383.24]]

Now DataTable can be populated by passing the data to arrayToDataTable() function:

var data = google.visualization.arrayToDataTable({$column_chart_data});

1	var data = google.visualization.arrayToDataTable({$column_chart_data});

Putting them all together

$HTML =<<<XYZ
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">

      // Load the Visualization API and the charts package.
      google.load('visualization', '1.0', {'packages':['corechart']});

      // Column Chart
      google.setOnLoadCallback(drawColumnChart);

      function drawColumnChart() {
        var data = google.visualization.arrayToDataTable({$column_chart_data});

         var options = {
          title: 'Total Credits And Salary Averages Within Age Range',
          titleTextStyle: {fontName: 'Lato', fontSize: 18, bold: true},
          hAxis: {title: 'Age Range', titleTextStyle: {color: '#3E4827'}},
          height: 500,
          chartArea:{left:50,top:50,width:'100%',height:'85%'},
          legend: { position: "top" },
          colors:['#91A753','#C6D9AC']
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('column_chart_div'));
        chart.draw(data, options);
      }

      // Make the charts responsive
      jQuery(document).ready(function(){
        jQuery(window).resize(function(){
          drawColumnChart();
        });
      });

    </script>

    <div id="column_chart_div"></div>
XYZ;

    echo $HTML;
?>

$HTML =<<<XYZ

// Load the Visualization API and the charts package.

google.load('visualization', '1.0', {'packages':['corechart']});

// Column Chart

google.setOnLoadCallback(drawColumnChart);

function drawColumnChart() {

var data = google.visualization.arrayToDataTable({$column_chart_data});

var options = {

title: 'Total Credits And Salary Averages Within Age Range',

titleTextStyle: {fontName: 'Lato', fontSize: 18, bold: true},

hAxis: {title: 'Age Range', titleTextStyle: {color: '#3E4827'}},

height: 500,

chartArea:{left:50,top:50,width:'100%',height:'85%'},

legend: { position: "top" },

colors:['#91A753','#C6D9AC']

};

var chart = new google.visualization.ColumnChart(document.getElementById('column_chart_div'));

chart.draw(data, options);

}

// Make the charts responsive

jQuery(document).ready(function(){

jQuery(window).resize(function(){

drawColumnChart();

});

</script>

XYZ;

echo $HTML;

Note that corechart package can be used for different chart types including column charts.

Number Of People In Each Country in a Geo Chart

Preparing the data

Use this query:

SELECT country, count(id) AS Number FROM dummy_data GROUP BY country;

1	SELECT country, count(id) AS Number FROM dummy_data GROUP BY country;

Populating DataTable

$geo_chart_data = array(array("Country", "Population"));
foreach($results as $result)
 {
  $geo_chart_data[] = array($result['country'], (int)$result['Number']);
 }
$geo_chart_data = json_encode($geo_chart_data);

$geo_chart_data = array(array("Country", "Population"));

foreach($results as $result)

{

$geo_chart_data[] = array($result['country'], (int)$result['Number']);

}

$geo_chart_data = json_encode($geo_chart_data);

Now DataTable can be populated by passing the data to arrayToDataTable() function:

var data = google.visualization.arrayToDataTable({$geo_chart_data});

1	var data = google.visualization.arrayToDataTable({$geo_chart_data});

Putting them all together

$HTML =<<<XYZ
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">

      // Load the Visualization API and the charts package.
      google.load('visualization', '1.0', {'packages':['geochart']});

      // Geochart
      google.setOnLoadCallback(drawRegionsMap);

      function drawRegionsMap() {
       var data = google.visualization.arrayToDataTable({$geo_chart_data});

       var options = {colorAxis: {colors: ['#FFFFFF', '#048ABF', '#025172', '#023E5A', '#013453']}};

       var chart = new google.visualization.GeoChart(document.getElementById('geo_chart_div'));
        chart.draw(data, options);
        }

      // Make the charts responsive
      jQuery(document).ready(function(){
        jQuery(window).resize(function(){
          drawRegionsMap();
        });
      });

    </script>

    <p style="color: black;"><b>Number Of People In Each Country</b></p>
    <div id="geo_chart_div" style="max-width: 650px; max-height: 600px;"></div>
XYZ;

    echo $HTML;
?>

$HTML =<<<XYZ

// Load the Visualization API and the charts package.

google.load('visualization', '1.0', {'packages':['geochart']});

// Geochart

google.setOnLoadCallback(drawRegionsMap);

function drawRegionsMap() {

var data = google.visualization.arrayToDataTable({$geo_chart_data});

var options = {colorAxis: {colors: ['#FFFFFF', '#048ABF', '#025172', '#023E5A', '#013453']}};

var chart = new google.visualization.GeoChart(document.getElementById('geo_chart_div'));

chart.draw(data, options);

}

// Make the charts responsive

jQuery(document).ready(function(){

jQuery(window).resize(function(){

drawRegionsMap();

});

</script>

<p style="color: black;"><b>Number Of People In Each Country</b></p>

XYZ;

echo $HTML;

Note that the package name for Geo Charts is geochart. The other thing is that, at the time of writing Geo Charts don’t support title as an option.

Demo

Here is the interesting part! You will see all the three charts together.

Troubleshooting

In case if your chart doesn’t appear in the page use Google Chrome Inspector to find the possible errors. For example, if for some reason Google Visualization library is not loaded you will get error: Cannot read property ‘DataTable’ of undefined or Cannot read property ‘arrayToDataTable’ of undefined
Remember if page A sends an AJAX request to page B to get a chart, make sure that Google Visualization library has already loaded in page A.

6 Comments

Daniel
19 April 2015 at 6:20 pm

You code is great but I would gladly appreciate if you provide a full code for download that one can drop and run the way it is

talha Rehman
3 June 2015 at 6:27 pm

Thanks a lot.

Joshua
23 June 2015 at 4:13 am

Hi Ehsan,

This looks like a really good tutorial, but I’m missing the part where the database connection actually made? It would seem that I’m stuck right outta the starting gate.

Think you could point a fella in the right direction so he can start playing with this neat little toy? Much appreciated. Thanks!

dinh tran cong
24 June 2015 at 9:29 pm

hi! (i’m sorry for my ELish is bad!) and thanks ur share about chart code. You please help me in first example, pie chart, $results variable detail in MySQL query. I tryed to query but when use print_r($results) is not like you show on screen!

Please help me full MySQL query code of $results varieble.
Thanks u very mucrh!

Alice M.
6 July 2015 at 10:38 pm

Hello,
I tried to include API data in my Google Geo Chart, but it doesn’t work.

In my MyAPI.php : function Mappa($sesso, $anno){
header(‘Content-Type: application/json’);
require(‘../php/config.php’);
#CONNESSIONE AL DATABASE
$con=mysqli_connect(“localhost”, “root”, “ginnastica”, “progetto”);
// Check connection
if (mysqli_connect_errno()) #SE C’È UN ERRORE DI CONNESSIONE, MI FERMO SUBITO
{
echo “Failed to connect to MySQL: ” . mysqli_connect_error();
}
$recordsTot=array();
$recordsTot = select($mysqli,”SELECT Country, Value AS data FROM disMondiale WHERE SUBJECT=’$sesso’ AND TIME=’$anno'”);
foreach($results as $result)
{
$geo_chart_data[] = array($recordsTot[‘Country’], (int)$recordsTot[‘data’]);
}
$geo_chart_data = json_encode($geo_chart_data);
}

in my JS:
function Mappa(node, anno){
var annoC=anno;
$.getJSON(“./api/getByTotMappa.php?anno=”+annoC+”&src=”, function(data){
google.setOnLoadCallback(creaMappa(data, node))});
}
function creaMappa(series,node){
var data = google.visualization.arrayToDataTable({series});

var options = {colorAxis: {colors: [‘#FFFFFF’, ‘#048ABF’, ‘#025172’, ‘#023E5A’, ‘#013453’]}};

var chart = new google.visualization.GeoChart(document.getElementById(‘regions_div’));

chart.draw(data, options);
}

And then:
$(document).ready(function() {
google.load(‘visualization’, ‘1.0’, {‘packages’:[‘geochart’]});
var node = “container”;
Mappa(node2,2011);}

getByTotMappa.php:

What am I doing wrong? Can you help me? There’s no visualization, the containere is white.

Mitchel
20 July 2017 at 10:40 pm

Whenever I tried to add this code in tcpdf file it shows error. So I am adding each html part in $html.=”; section, please guide me accordingly.

Keep Coding

Never Stop Learning

Data Visualisation with the Google Chart, PHP and MySQL

Dummy data generation

The percent of people within age range in a pie chart

Preparing the data

Populating DataTable

Loading necessary libraries

Putting them all together

Total Credits And Salary Averages Within Age Range in a Column Chart

Preparing the data

Populating DataTable

Putting them all together

Number Of People In Each Country in a Geo Chart

Preparing the data

Populating DataTable

Putting them all together

Demo

Troubleshooting

6 Comments

Leave a Reply Cancel reply

Ads

Categories

Monthly Archive

Pages

Tags

Recent Posts

Keep Coding

Never Stop Learning

Data Visualisation with the Google Chart, PHP and MySQL

Dummy data generation

The percent of people within age range in a pie chart

Preparing the data

Populating DataTable

Loading necessary libraries

Putting them all together

Total Credits And Salary Averages Within Age Range in a Column Chart

Preparing the data

Populating DataTable

Putting them all together

Number Of People In Each Country in a Geo Chart

Preparing the data

Populating DataTable

Putting them all together

Demo

Troubleshooting

Previous post

Next post

6 Comments

Leave a Reply Cancel reply

Ads

Categories

Monthly Archive

Pages

Tags

Recent Posts