Recently I was asked to create a webpage to summarize some data from a MySQL database for analysis. Creating webpages and running SQL queries isn’t something that I do on a regular basis; I mostly administrate the servers that these services run on. But I figured, why not?! I’m familiar with all of this stuff. At the same time, why don’t we create a nice Google pie chart with this data?

First, we need to see the data that we are querying. Take a look at the sample table below:

id      user_id         activity_id     date            entry   points
5262    214             1               5/20/2011       1       1
5263    214             2               5/20/2011       1       1
5264    214             3               5/20/2011       1       1
5265    214             4               5/20/2011       4       4
5266    214             6               5/20/2011       1       1
5267    214             7               5/20/2011       1       1
5268    214             10              5/20/2011       1       1
5269    214             14              5/20/2011       2       2
5270    214             19              5/20/2011       1       1
5271    214             34              5/20/2011       1       1
5272    214             21              5/20/2011       3       3
5273    214             24              5/20/2011       0       0
5290    214             1               5/21/2011       1       1
5291    214             2               5/21/2011       1       1
5292    214             3               5/21/2011       1       1
5293    214             4               5/21/2011       3       3
5294    214             5               5/21/2011       6       2
5295    214             7               5/21/2011       1       1
5296    214             10              5/21/2011       1       1
5297    214             13              5/21/2011       1       1
5298    214             19              5/21/2011       1       1
5299    214             21              5/21/2011       2       2
5300    214             24              5/21/2011       0       0

Basically what we wanted to know, is how many points were earned for each activity. For example, 5 points were earned for activity_id 21. Now, of course, we need to do some SQL joins to get the user and activity names. After all our joins, sums, and such, we end up with data like the following:

username      name                          label                           pointsum
Lassie        Drinking Water                Glasses                         7
Lassie        Minimum Exertion              Enter the number of 1/2 hours   5
Lassie        Eat Fruits                    Recommended amount              2
Lassie        Healthy Eating Choices        Lunch                           2
Lassie        Take The Stairs               Round trips                     2
Lassie        Healthy Eating Choices        Breakfast                       2
Lassie        Eat Vegetables                Some                            2
Lassie        Other                         Fun things today                2
Lassie        Buckle Up                     I did it                        2
Lassie        Healthy Eating Choices        Dinner                          2
Lassie        Walk                          Miles                           1
Lassie        Eat Fruits                    Some                            1
Lassie        Charitable Activity           I did it                        1

Alright, now that we can get the correct data, we just need a webpage to display this stuff!

First, go and download jquery. Next we will create an index.php file to put some stuff in! (NOTE: I like to download the jsapi file from Google and load it from my webserver, instead of making it load from Google’s servers. If you wish to do that, save the jsapi and replace the src="http://www.google.com/jsapi" in the code below with the location where you saved the file.)
Here is the index.php file:

<html>
<head>
  <!--Load the AJAX API-->
  <script type="text/javascript" src="http://www.google.com/jsapi"></script>
  <script type="text/javascript" src="jquery-1.9.1.min.js"></script>
  <script type="text/javascript">

  // Load the Visualization API and the piechart,table package.
  google.load('visualization', '1', {'packages':['corechart','table']});

  function drawItems(num) {
    var jsonPieChartData = $.ajax({
      url: "getpiechartdata.php",
      data: "q="+num,
      dataType:"json",
      async: false
    }).responseText;

    var jsonTableData = $.ajax({
      url: "gettabledata.php",
      data: "q="+num,
      dataType:"json",
      async: false
    }).responseText;

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

    // Instantiate and draw our pie chart, passing in some options.
    var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
    chart.draw(piechartdata, {
      width: 700,
      height: 500,
      chartArea: { left:"5%",top:"5%",width:"90%",height:"90%" }
    });

    // Instantiate and draw our table, passing in some options.
    var table = new google.visualization.Table(document.getElementById('table_div'));
    table.draw(tabledata, {showRowNumber: true, alternatingRowStyle: true});
  }

  </script>
</head>
<body>
  <form>
  <select name="users" onchange="drawItems(this.value)">
  <option value="">Select a dogg:</option>
  <?php
    $dbuser="database_username";
    $dbname="database_name";
    $dbpass="database_password";
    $dbserver="database_server";
    // Make a MySQL Connection
    $con = mysql_connect($dbserver, $dbuser, $dbpass) or die(mysql_error());
    mysql_select_db($dbname) or die(mysql_error());
    // Create a Query
    $sql_query = "SELECT id, nickname FROM user ORDER BY nickname ASC";
    // Execute query
    $result = mysql_query($sql_query) or die(mysql_error());
    while ($row = mysql_fetch_array($result)){
    echo '<option value='. $row['id'] . '>'. $row['nickname'] . '</option>';
    }
    mysql_close($con);
  ?>
  </select>
  </form>
  <div id="chart_div"></div>
  <div id="table_div"></div>
</body>
</html>

So, what does all of that stuff do? Basically, we load the necessary files for the Google charts, and set up some functions to get our data and draw the charts. We then pull all the user names and ID’s from the SQL database and create a dropdown box with the names of our users. This will allow us to select a user from a dropdown box, and display the data for that user in a Google pie chart, along with the raw data in a table below it!

When we select a user from our dropdown box, the user’s ID number is sent to the drawItems() function. This drawItems() function takes the user’s ID number and sends it to the getpiechartdata.php and gettabledata.php files, where some additional SQL queries are run, and the desired user data is returned. The data is returned in JSON format, for parsing by the Google graph functions.

Here is the getpiechartdata.php file:

<?php
  $q=$_GET["q"];
  $dbuser="database_username";
  $dbname="database_name";
  $dbpass="database_password";
  $dbserver="database_server";

  $sql_query = "SELECT nickname, name, j2.label, j2.pointsum FROM user JOIN ( SELECT j1.user_id, j1.label, name, hover, j1.pointsum FROM activityfield JOIN ( SELECT user_id, activity_id, label, field_id , SUM( points.points ) AS PointSum FROM points JOIN activity ON points.activity_id = activity.id WHERE points.user_id=" . $q . " GROUP BY points.user_id, points.activity_id, activity.label, activity.field_id ORDER BY points.activity_id ASC ) AS j1 ON activityfield.id = j1.field_id ) AS j2 ON j2.user_id = user.id WHERE pointsum > 0 ORDER BY j2.pointsum DESC;";

  $con = mysql_connect($dbserver,$dbuser,$dbpass);
  if (!$con){ die('Could not connect: ' . mysql_error()); }
  mysql_select_db($dbname, $con);
  $result = mysql_query($sql_query);
  echo "{ \"cols\": [ {\"id\":\"\",\"label\":\"Name-Label\",\"pattern\":\"\",\"type\":\"string\"}, {\"id\":\"\",\"label\":\"PointSum\",\"pattern\":\"\",\"type\":\"number\"} ], \"rows\": [ ";
  $total_rows = mysql_num_rows($result);
  $row_num = 0;
  while($row = mysql_fetch_array($result)){
    $row_num++;
    if ($row_num == $total_rows){
      echo "{\"c\":[{\"v\":\"" . $row['name'] . "-" . $row['label'] . "\",\"f\":null},{\"v\":" . $row['pointsum'] . ",\"f\":null}]}";
    } else {
      echo "{\"c\":[{\"v\":\"" . $row['name'] . "-" . $row['label'] . "\",\"f\":null},{\"v\":" . $row['pointsum'] . ",\"f\":null}]}, ";
    }
  }
  echo " ] }";
  mysql_close($con);
?>

Here is the gettabledata.php file:

<?php
  $q=$_GET["q"];
  $dbuser="database_username";
  $dbname="database_name";
  $dbpass="database_password";
  $dbserver="database_server";

  $sql_query = "SELECT nickname, name, j2.label, hover, j2.pointsum FROM user JOIN ( SELECT j1.user_id, j1.label, name, hover, j1.pointsum FROM activityfield JOIN ( SELECT user_id, activity_id, label, field_id , SUM( points.points ) AS PointSum FROM points JOIN activity ON points.activity_id = activity.id WHERE points.user_id=" . $q . " GROUP BY points.user_id, points.activity_id, activity.label, activity.field_id ORDER BY points.activity_id ASC ) AS j1 ON activityfield.id = j1.field_id ) AS j2 ON j2.user_id = user.id WHERE pointsum > 0 ORDER BY j2.pointsum DESC;";

  $sql_query2 = "SELECT nickname, sum(j2.pointsum) as total FROM user JOIN ( SELECT j1.user_id, j1.label, name, hover, j1.pointsum FROM activityfield JOIN ( SELECT user_id, activity_id, label, field_id , SUM( points.points ) AS PointSum FROM points JOIN activity ON points.activity_id = activity.id WHERE points.user_id=" . $q . " GROUP BY points.user_id, points.activity_id, activity.label, activity.field_id ORDER BY points.activity_id ASC ) AS j1 ON activityfield.id = j1.field_id ) AS j2 ON j2.user_id = user.id WHERE pointsum > 0 GROUP BY user.nickname, user.id ORDER BY j2.pointsum DESC;";

  $con = mysql_connect($dbserver,$dbuser,$dbpass);
  if (!$con){ die('Could not connect: ' . mysql_error()); }
  mysql_select_db($dbname, $con);
  $result = mysql_query($sql_query);

  echo "{ \"cols\": [ {\"id\":\"\",\"label\":\"Nickname\",\"pattern\":\"\",\"type\":\"number\"}, {\"id\":\"\",\"label\":\"Name\",\"pattern\":\"\",\"type\":\"number\"}, {\"id\":\"\",\"label\":\"Label\",\"pattern\":\"\",\"type\":\"number\"}, {\"id\":\"\",\"label\":\"Hover\",\"pattern\":\"\",\"type\":\"number\"}, {\"id\":\"\",\"label\":\"PointSum\",\"pattern\":\"\",\"type\":\"number\"} ], \"rows\": [ ";

  $total_rows = mysql_num_rows($result);
  while($row = mysql_fetch_array($result)){
    echo "{\"c\":[{\"v\":\"" . $row['nickname'] . "\",\"f\":null},{\"v\":\"" . $row['name'] . "\",\"f\":null},{\"v\":\"" . $row['label'] . "\",\"f\":null},{\"v\":\"" . $row['hover'] . "\",\"f\":null},{\"v\":\"" . $row['pointsum'] . "\",\"f\":null}]}, ";
  }

  $result = mysql_query($sql_query2);
  while($row = mysql_fetch_array($result)){
    echo "{\"c\":[{\"v\":\"" . $row['nickname'] . "\",\"f\":null},{\"v\":\"" . "\",\"f\":null},{\"v\":\"" . "\",\"f\":null},{\"v\":\" Total \",\"f\":null},{\"v\":\"" . $row['total'] . "\",\"f\":null}]}";
  }

  echo " ] }";
  mysql_close($con);
?>

In the gettabledata.php file above, we run a second SQL query to get the total of all our points, to display as the bottom line in our table.

And finally, here is what it all looks like when finished:

Google Pie Chart and Table

Want to see it in action? Check out my working example here! View the raw sql data used in this example over here.

You can also easily create many different types of Google graphs using SQL data, just change the appropriate sections of code within the drawItems() function to draw the graph you want!

For more examples, chart types, and other goodies check out http://code.google.com/apis/chart/.