Google Chart Tools with JSON, jQuery and mySQL/PHP



Google Chart Tools is a nice library for data visualisation as charts. It is free and provides many chart types which can adapted easily, but not neigher less suitable.

Google Chart Tools with JSON - chart

Screenshot: Chart drawn with Google Chart Tools, mySQL/PHP and JSON

The chart libray exepts an Javascript array. This example gets the data from a mySQL database, which carry the data from server to the client with JSON.

JSON:

The major important approach is to encode the data-array to JSON on server side and parse the data-array on client side. I prefer for this example jQuery, because it is frequentyl used. Other JSON Javascript libraries were possible but parsing is mandatory also on client side, the returned JSON string from server.

Encode JSON with PHP on server side:

echo json_encode($data); 

Parse the JSON string with native Javascript API-object "window.JSON" on client side. Browsersupport [1]

window.JSON.parse(jsonData); 

PHP:

Now we are ready for getting the data out of the database with PHP.

Set up an array and the returned data have to represents also this array structure. See the data structure at the link above and build it on server side.


<?php
    $dbhost="localhost";
    $dblogin="root";
    $dbpwd="";
    $dbname="myDB";
       
    $db =  mysql_connect($dbhost,$dblogin,$dbpwd);
    mysql_select_db($dbname);    
	
    $day = date('d');
    $month = date('m');
    $lastMonth = (string)($month-1);	
    $lastMonth = strlen($month - 1) == 1? '0'.$lastMonth : $lastMonth;
    
    $SQLString = "SELECT 	
		    count(analytics.day) as counts,
		    analytics.day, month,
		    date FROM analytics  
		    WHERE year = '2012' AND month = '$month' 
		    OR (month = '$lastMonth' and day > '$day') 
		    GROUP BY day, month 
		    ORDER BY date asc";		
										
    $result = mysql_query($SQLString);    
    $num = mysql_num_rows($result);   

# set heading	
    $data[0] = array('day','counts');		
    for ($i=1; $i<($num+1); $i++)
    {
        $data[$i] = array(substr(mysql_result($result, $i-1, "date"), 5, 5),
			(int) mysql_result($result, $i-1, "counts"));
    }	
    echo json_encode($data);
    mysql_close($db);
?>

The tracker.php returns something like this:

[["day","counts"],["06-14",3],["06-15",22],...,["07-12",22]] 

[]: An Javascript array-object. (Important: But not yet a object which understands the Google Chart Tools function .arraytodatatable(). For parse this returned JSON data string to the apropriate javascript object, see the method below .parseJSON() from jQuery.)

Google JSAPI API and jQuery:

Get the data on client side we use also a jQuery function for AJAX interactions, jQuery.ajax() [2]. For Google Chart Tools we need the Google JSAPI API, which loads with Javascript the Google Visualization and the chart itself [3].

From up to now it depends on the transfered datas with Javascript and of corse jQuery libraries. It means, do not forget to parse the JSON string delivered from server in a Google Chart Tools known JSON object.


<html>
<head>
    <title>Kometschuh.de Tracker</title>
    <!-- Load jQuery -->
    <script language="javascript" type="text/javascript" 
        src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js">
    </script>
    <!-- Load Google JSAPI -->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
        google.load("visualization", "1", { packages: ["corechart"] });
        google.setOnLoadCallback(drawChart);

        function drawChart() {
            var jsonData = $.ajax({
                url: "php/tracker.php",
                dataType: "json",
                async: false
            }).responseText;

            var obj = window.JSON.stringify(jsonData);
            var data = google.visualization.arrayToDataTable(obj);

            var options = {
                title: 'Kometschuh.de Trackerdaten'
            };

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

    </script>
</head>
<body>
    <div id="chart_div" style="width: 900px; height: 500px;">
    </div>
</body>
</html>

download: Tracker.zip


[1] Browsersupport for JSON parsing http://caniuse.com/#feat=json.

[2] The jQuery.ajax() function http://api.jquery.com/jQuery.ajax/. It needs the jQuery library http://jquery.com/

[3] https://developers.google.com/chart/interactive/docs/basic_load_libs

© Kometschuh.de | Impressum | GitHub | Facebook |