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:
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/.
