Creating a Google Pie Chart using SQL data

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:

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:

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:

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.

You should be able to use the same basic setup with SQLite. Check out the getpiechartdata.php and gettabledata.php files above, and modify them to connect to your database. Make sure that you are formatting the data correctly before passing it to the Google API.

I have added a link to a copy of the raw sql data, dumped directly from this example. The link is just after the link to the working example page. There may be some extra fields and data in the data dump that isn’t necessary; I copied parts of the original work from another database I had running.

You should only need to create the database along with a user that has access to the database, then dump that info into it.

Thanks for this tutorial Creating a Google Pie Chart using SQL data, I am really interested in know more. I am new to sqlite, pHp and jquery, I have a thesis on this to connect a web page to database and display the various data information in graphs, csv and other format. I need help on where should i start and how should i do this base on the example you did. Any help with be appreciated. thks

Hi, I am trying to use your way to draw the charts / tables I want to.
But it will not succeed.
This is the output from my php-file that takes the data from a mysql database (2 columns = datatime & value):

I don’t see anything easily noticeable that would prevent your table from showing. Make sure that you are loading all the necessary javascript before loading the Visualization API. I would recommend trying to use all of the data from my examples to get a working table, then modifying it to fit your needs. Hopefully this will help!

thanks!!
I just found that the date must be in format ‘Date (year, month, day, hour, minute, second)’.
My problem is now how can I convert a format like “2012-11-12 15:54:07″ in that format with php?
I didnt’ found a solution yet, also no way to extract the items (year, month etc) from the datetime from mysql.
Any suggestion would be usefull.

All the necessary code is on here already. Make sure that you have downloaded the jquery javascript, the index.php, getpiechartdata.php, and gettabledata.php files. If you are missing any of these files, it could lead to the symptoms you described.

I would do getpiechartdata.php bit different since it’s quite robust and may not work always.
$con = mysql_connect($dbserver,$dbuser,$dbpass);
if (!$con){ die('Could not connect: ' . mysql_error()); }
mysql_select_db($dbname, $con);
$result = mysql_query($sql_query);

For the second part, you will need to create an sql query to pull out the specific values you want, then add them up, calculate the percentage, then display that. You will have to figure the specific SQL joins to use (something like select task_id, task_student_id, complete, not_complete from table task_student_id) then create an array to manipulate the results, kinda like this:

This will give you an array $records that should hold the specific values requested in your query. Then you just add up the number of complete and incomplete to find the total number of assignments, and divide complete/total and incomplete/total to get your percentages.

Try reworking your drawitems function to match what I have; you’re missing the .responseText at the end of the function, and you have an extra section that I don’t have. You might try just copying my code and tweaking it to work with your database.

Hi Sophie
what are the joins you are using? In the section where you have mentioned that after joins that is the table you got..what is the query for it as I am unable to find where the id=5262 onwards are coming from?

Make sure that your SQL query works correctly. I actually had to change some of my queries to reflect a recent change in MySQL. Take your query by itself and run it through MySQL either from the command line, or using something like phpMyAdmin. Also make sure that you are setting the mysql connection to a variable like this: $con = mysql_connect($dbserver, $dbuser, $dbpass) or die(mysql_error());

Awesome, it’s useful for me, thank you so much. I have a problem, can you help me, please ?
I want to show the lable (name, percentage) on chart like this link:http://lephuongphoto.com/images/piechart.png
Hope receive your reply soon.
Thank you so much.

Thank you very much, I was trying for 10 hours to get something like this just following instructions from the other sites and it didn’t work, but last night I tried your example and it really helped!!! :) once again thank you very much!!!!! :)

Thanks for sharing this. I spent so many hours over the last weeks trying to make my chart work and trying every other instruction available on the web. And losing my sanity and patience in the process… Your method worked for me!

The ‘newbie’ friendly documentation on Google charts and MySQL has been really tough to find. I had a lot of trouble understanding formating things for JSON output. Your demo where you basically just echoed your fields and wrote them as JSON instead of encoding them into an array really helped make sense of things for me. I really appreciate your documenting your example and sharing for everyone!

Another to go about this is to use the Google Visualization Data Source I developed for PHP (translated from the Java library written by Google). It will allow you to use Google Query Language syntax to query a MySQL table. See the code and documentation here: https://github.com/bggardner/google-visualization-php