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:
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
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:
1 2 3 4 5 6 7 8 9 10 |
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:
1 2 3 4 5 6 |
$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:
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:
1 2 3 4 |
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:
1 2 3 4 |
<script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> google.load('visualization', '1.0', {'packages':['corechart']}); </script> |
Putting them all together
Here is the final code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
<?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; ?> |
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:
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
1 2 3 4 5 6 |
$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);
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:
1 |
var data = google.visualization.arrayToDataTable({$column_chart_data}); |
Putting them all together
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
$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; ?> |
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:
1 |
SELECT country, count(id) AS Number FROM dummy_data GROUP BY country; |
Populating DataTable
1 2 3 4 5 6 |
$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:
1 |
var data = google.visualization.arrayToDataTable({$geo_chart_data}); |
Putting them all together
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
$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; ?> |
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.
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
3 June 2015 at 6:27 pm
Thanks a lot.
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!
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!
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.
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.
16 August 2019 at 3:22 am
Great example! Very thorough.
Your example data output helped me figure out what was wrong with the way my data was returning. – I kept getting “not an array” errors.