Do Funny Stuff with the Google Charts API and PHP

I think, whenever we see a chart in a magazine, in books or applications our brain say’s to us Yeepie!!!
Don’t read these stupid texts or tables! Just look at the green or yellow line and hope that they are above the other lines or hope that your opinion is the biggest part of the pie. This saves us a lot of work and in my opinion we should concentrate on drawing beautiful colorized pie charts, instead of writing long boring articles.

But the question is: How do I draw these beautiful colorized pie charts? The simple answer is: you don’t have to, because google will draw them for you and you just have to tell them what to draw via the google charts api.

Bildschirmfoto vom 2013-03-06 00:42:36

Today I want to make the world a better place (a place with more line charts, pie charts, column charts, bubble charts, bar charts) and write a small tutorial on how to use this wonderful API Google gives us in combination with a very small PHP backend.

Bildschirmfoto vom 2013-03-06 00:27:36

For simple/static Charts it is pretty easy to use the google charts api. You find an example directly from google here.

But our application should have a more complex real world usecase, so I decided to program a small breakfast rating web application. This Application allows you to rate the quality of the breakfast your roommate prepares you day by day. If this is not a real world scenario for you, then you should get better roommates! This should show you how to program a chart with ajax communication and the working PHP-backend which will deliver the date for the chart.

As technical environment I use an Apache Webserver with the PHP 5 module installed. As data persistence layer I’ll use a SQLite Database, but with small changes this example will also work with every other conventional SQL Database like MySql. The running application can be seen here and the whole instantly running project can be downloaded/checked out here (github link).

First: Define Project Structure

At first of all we need to define our project structure. To simplify this project I’ll only work with a backend.php where the servercoding will be placed and an index.html which is our frontend coding.

The next step is to define our database structure, for this Example it is pretty easy we just need an id (because every table of mine has an id field), a date and a rating field.

 
CREATE TABLE IF NOT EXISTS voting (
						id INTEGER PRIMARY KEY,
						date INTEGER,
						rating INTEGER )

To access the database and initially build the table we create the backend file with a simple switch case to separate the different actions and tasks of the backend. Because we are using the GET variables we can choose the action comfortable with URL parameters. The first two lines start the database “connection” (It isn’t really a connection because we just access a file via the SQLite Library) and set the error handling of this connection to Exceptions, to easily handle situations we didn’t expected.

setAttribute(PDO::ATTR_ERRMODE,
		PDO::ERRMODE_EXCEPTION);

if(!isset($_GET['action'])){
	//Redirect to mainpage
	header ("Location: ./index.html");
}else{
	switch ($_GET['action']){
		case 'install':
			try {
				//Action to initial create the database table
				$file_db->exec("CREATE TABLE IF NOT EXISTS voting (
						id INTEGER PRIMARY KEY,
						date INTEGER,
						rating INTEGER
				)");
				echo "Datebase installed correctly";

			} catch (PDOException $e) {
				echo "FU** this should not happen: ".$e->getMessage();
			}
			break;
	}
}
?>

Now we are able to create our first database with opening the URL http://localhost/backend.php?action=install .

Second: Simple Datainput.

After that our database must be filled with ratings. For this I easily added another case in the switch case control structure:

	switch ($_GET['action']){
		case 'install':
			//...
		case 'addRating':

			//Get the time for 
			$date = time();
			$rating = $_POST['rating'];

			$insert = "INSERT INTO voting (date, rating)
                VALUES (:date, :rating)";
			$stmt = $file_db->prepare($insert);

			// Bind parameters to statement variables
			$stmt->bindParam(':date', $date);
			$stmt->bindParam(':rating', $rating);

			//And write the date into the database
			$stmt->execute();
			//Redirect to index file
			header ("Location: ./index.html"); 	
			break;

As another step we need a small form to allow the user to rate the breakfast. As you can see I’m using POST parameters for the rating result.

<form method="post" action="backend.php?action=addRating">
	 <label for="radio" class="inline">1 Star</label>  <label for="radio" class="inline">2 Star</label>  <label for="radio" class="inline">3 Star</label>  <label for="radio" class="inline">4 Star</label> 
</form>

Third: Dynamic Chart (JSON, SQLite)

Our initialization and the input parts are done, now we want to do the magical graph stuff. Instead of just writing the rating values into a -tag in the index.html we will provide the data with a separated request as JSON format. This has more than one reason: The index.html stays static (much faster), datacontent is seperated from the layout and we can reload the data without a page-refresh.

The following coding shows how the JSON output looks like.

{ "cols" : [ { "id" : "",
        "label" : "Date",
        "pattern" : "",
        "type" : "string"
      },
      { "id" : "",
        "label" : "Rating",
        "pattern" : "",
        "type" : "number"
      }
    ],
  "rows" : [ { "c" : [ { "v" : "10/11/12" },
            { "v" : 4 }
          ] },
      { "c" : [ { "v" : "11/11/12" },
            { "v" : 3 }
          ] },
      { "c" : [ { "v" : "13/02/13" },
            { "v" : 3 }
          ] }
    ]
}

To generate the JSON I simply added another case to the switch/case in the backend.php :

case 'getRatings':

			//Select 
			$result = $file_db->query('SELECT * FROM voting');

			echo '{
			  "cols": [
					{"id":"","label":"Date","pattern":"","type":"string"},
					{"id":"","label":"Rating","pattern":"","type":"number"}
				  ],
			  "rows": [';

			foreach($result as $row){

				echo '{"c":[{"v":"'.date('d/m/y',$row['date']).'"},{"v":'.$row['rating'].'}]},';
			}

			echo ' ]
			}';

			break;

To Enable the Google Charts API Service on your site you have to use following JavaScript include:

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

Fourth: Display the chart

Until now we haven’t displayed anything, but the hardest part is done. The following coding describes the creation of the chart, which renders the chart into the given div containter(“chart_div”). The API allows you also to use a more generic interface to create charts with the chartswrapper.

<script type="text/javascript">
	// Load the Visualization API and the piechart package.
	google.load('visualization', '1', {
		'packages' : [ 'corechart' ]
	});

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

	function drawChart() {
		var jsonData = $.ajax({
			url : "backend.php?action=getRatings",
			dataType : "json",
			async : false
		}).responseText;

		// Create our data table out of JSON data loaded from server.
		var data = new google.visualization.DataTable(jsonData);

		// Instantiate and draw our chart, passing in some options.
		var chart = new google.visualization.LineChart(document
				.getElementById('chart_div'));
		chart.draw(data);
	}
</script>

This should be everything you need to build this small (usefull) application.

As said above the full coding is on github, ready to clone.
Also as decribed, you can use the running Apllication here.

I hope this artice helped you to understand the Google Charts API. If you have any questions or corrections feel free to write a comment :-)

Posted on by Hendrik in Programming Leave a comment

Add a Comment