Keep Coding

Never Stop Learning

Data Visualisation with the Google Chart, PHP and MySQL

Pie ChartGoogle 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:

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:

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:

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:

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:

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

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:

Putting them all together

Here is the final code:

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:

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:

Here is the output of echo ($column_chart_data);

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

Putting them all together

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:

Populating DataTable

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

Putting them all together

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.

5 Comments

  1. 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

  2. talha Rehman

    3 June 2015 at 6:27 pm

    Thanks a lot.

  3. 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!

  4. 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!

  5. 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.

Leave a Reply

Your email address will not be published.

*