Create Dynamic Time User Graph using Google Charts – A Practical Tutorial

In my last post, we have studied the basics of Google Chart. Google Charts helps us display the dry-data in a very beautiful visualization.

If you look around the web, you’ll find that there is a lot of buzz about Google charts but there are not many tutorials which demonstrate how to  use Google Charts. So I came up with this tutorial.

In this tutorial I will share how to create time user graph using Google Charts , PHP and MYSQL.  Here are the steps :

  1. Load the Google Charts AJAX api: jsapi .
  2. Load the Visualization API Library and add package name corechart.
  3. Then, set google callback function and call `getData()` function which will fetch data from the database.
  4. In the getData() function, we will retrieve an array of data and will push the data in the drawChart() method.
  5. In the drawChart() method, we will push the array data to tables and will draw line chart with the data.

For this tutorial we will be using a DB table as a data source. The table contains users and their date of registration.  Here, we will display the number of registered users on a particular date.  Lets start with the tuorial:

  1. Load the Google Charts AJAX  api: `jsapi`
    <!-- Load the AJAX API -->
    <script type="text/javascript" src="https://www.google.com/jsapi" > </script>
  2. Load the Visualization API Library and add package name `corechart`
    <script type="text/javascript" >
        // Load the Visualization API library and add package name.
        google.load( 'visualization', '1.0', { 'packages' :[ "corechart" ] });
    </script>
  3. Set Google callback function and call getData() function
    <script type="text/javascript" >
        google.setOnLoadCallback( getData );
    </script>
  4. Call getData() function and fetch the data from the database

    In getData() method, we will write ajax method to fetch the data. If the data is successfully received, we will call drawChart() function.

        /* Get data from the database */
        function getData() {
            jQuery.ajax({
                url: "ajax.php",
                type: 'GET',
                dataType: 'json',
                mimeType: 'multipart/form-data',
                contentType: false,
                cache: false,
                processData: false,
                success: function( data , jqXHR ) {
                    if( data == "null" ) {
                        // just in case
                    } else {
                        drawGraph( data );
                    }
                },
                error: function( textStatus ) {
                    console.log( "error. damm." );
                }
            });
        }

    Since, we need to fetch data from the database, so we will make connection by creating connection.php file and add the following code:

    Database Connection

    <?php
    define( 'DB_SERVER' , 'localhost' );
    define( 'DB_USERNAME' , 'root' );
    define( 'DB_PASSWORD' , 'qwerty' );
    define( 'DB_DATABASE' , 'DB_NAME' );
    
    class DB_Class {
        function __construct() {
            $connection = mysql_connect( DB_SERVER , DB_USERNAME , DB_PASSWORD ) or die( 'Oops connection error -> ' . mysql_error() );
            mysql_select_db( DB_DATABASE , $connection ) or die( 'Database error -> ' . mysql_error() );
        }
    }
    ?>

    After this, we will create another file as ajax.php and on the top, we will include connection.php file. Here we will fetch the data from the database which is required for displaying the line chart. Then we will convert the array in json object by using php json_encode() function.

    Fetch Data from Database

    <?php
    include_once 'include/connection.php';
    $db = new DB_Class();
    
    if($_GET) {
        $query = "select DATE( created_date ), COUNT(*)
                    from users
                    group by DATE( created_date )";
        $result = mysql_query( $query );
        $rows = array();
        while( $row = mysql_fetch_array( $result ) ) {
            $rows[] = array( '0' => $row['0'] , '1' => $row['1'] );
        }
        
        print json_encode($rows);
    }
    ?>
  5. Function drawChart() will draw the line chart with the received data
    /* Chart to render time-user graphs */
    function drawGraph( data ) {
        for(var i = data.length; i > 0; i--) {
            data[i] = data[i - 1];
        }
        data[0] = ['Date', 'Users'];
        console.log(data);
        var chartData = google.visualization.arrayToDataTable( data );
    
        var options = {
            title: ' Time - User Graph '
        };
    
        var chart = new google.visualization.LineChart( document.getElementById( 'chart_div' ) );
    
        chart.draw( chartData , options );
    }

Now let’s style with CSS

We will add the following style for designing. Create another file as style.css

* {
    margin: 0 auto;
    padding: 0;
}

body {
    background-color: #F2F2F2;
}

.container {
    margin: 0 auto;
    width: 920px;
    padding: 50px 20px;
    background-color: #fff;
}

h3 {
    text-align: center;
}

p {
    margin: 20px;
}

#chart_div {
    width: 900px;
    height: 500px;
}

Chart ErrorFigure1: Error on Charts ( Data column(s) for axis #0 cannot be of type string )

We will modify our php json_encode method and will add JSON_NUMERIC_CHECK parameter. This will tell json_encode to recognize numbers and abstain from wrapping them in quotes. To read more about it, please visit following link.

print json_encode($rows, JSON_NUMERIC_CHECK);

Final Markup

The complete index.php file would look like:

<!doctype html >
<html lang="en" >
<head>
<title> Create dynamic time user graph using Google Charts </title>
<meta charset="utf-8" >
<link href="css/style.css" rel="stylesheet" type="text/css" />
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js" > </script>
<!-- Load the AJAX API -->
<script type="text/javascript" src="https://www.google.com/jsapi" > </script>
<script type="text/javascript" >
    /* Get data from the database */
    function getData() {
        jQuery.ajax({
            url: 'ajax.php',
            type: 'GET',
            dataType: 'json',
            mimeType: 'multipart/form-data',
            contentType: false,
            cache: false,
            processData: false,
            success: function( data, jqXHR ) {
                if( data == "null" ) {
                    // just in case
                } else {
                    drawGraph( data );
                }
            },
            error: function( textStatus ) {
                console.log(" error. damm. ");
            }
        });
    }
    
    /* Initialization of Google Charts API */
    google.load( "visualization" , "1", { packages: [ "corechart" ] });
    google.setOnLoadCallback( getData );
    
    /* Chart to render time-user graphs */
    function drawGraph( data ) {
        for( var i = data.length; i > 0; i-- ) {
            data[i] = data[i - 1];
        }
        data[0] = [ 'Date', 'Users' ];
        console.log( data );
        var chartData = google.visualization.arrayToDataTable( data );

        var options = {
            title: ' Time - User Graph '
        };

        var chart = new google.visualization.LineChart( document.getElementById( 'chart_div' ) );

        chart.draw( chartData , options );
    }
</script>
</head>
<body>
    <div class="container" >
        <h3> Line Chart showing number of registered users </h3>
        
        <!-- Div that will hold the line chart -->
        <div id="chart_div" > </div>
    </div>
</body>
</html>

Time User GraphFigure2: Shows time user graph using Google Charts

When you hover on the peak points, it will give you the detailed information of the number of users or data. And yes, you can customize the look and feel of the Google Charts.

Google Charts Hover ContentFigure3: Displays the content on hover

You can also change line chart to bar chart quite easily. Just modify one line and it is done.

var chart = new google.visualization.BarChart(document.getElementById('chart_div'));

Bar ChartFigure4: Displays the content in bar graph

Conclusion

Google chart is simple and powerful tool. You just need to get the data in the correct format and push array into dataTables. You can re-design Google Charts by selecting its and colors option and passing those as entries to the chart’s draw() method. Each chart types has different parameters that need to understand individually. Refer to the Google Charts Documentation for each chart type to see complete options list. Did you draw any interesting chart? Please share with us and let us know if you find any difficulty in creating Google Charts.

About Mohit Bhansali

A hyperactive mind with a passion for getting acquainted with the latest technology.

Comments

  1. Sourav Mondal says:

    Hi, this post saved my day. I got this link from some FB groups (which I forgot now). I was searching to implement Google Charts in my application back-end to display insights of the website. But I wasn’t able to do that. Finally, using this tutorial, I am able to implement that.

    Problem is, the UI of my website doesn’t match with default color of Google Charts. Please tell me, how do I change the background color and if possible, inner colors too?

    Thanks in advance.

    • Hey Sourav,

      I will ask Mohit to chip in with his suggestions.

      -Ankit

    • Mohit Bhansali says:

      Hi Sourav. I am glad that this post helped you. Your query is quite simple. Just add options variable before chart.draw method like we did for title. Try the following code:
      var options = {
      title: 'title',
      backgroundColor: '#D9D9D9,
      colors: ['red'],
      };

  2. El hombre no vive de otra cosa que de religion o de ilusiones – Giacomo
    Leopardi

Speak Your Mind

*