Geckoboard, PHP, and MySQL

The GoVoluntr office is always buzzing – lots of new and exciting things are always happening. One of the latest projects we are just starting is GoVoluntr v2. We are very excited about it, refining the GoVoluntr process even more to make it the easiest platform to volunteer, earn rewards, track social responsibility and more! Although it is many months away, it inspired us to start a new series called ‘Tech of GoVoluntr’. In this much more geeky series, you’ll get a sneak peak into the technologies and processes that help GoVoluntr run. So, let’s kick it off with metrics.

Metrics as a startup are key. The right metrics allow your team to make important decisions quickly and more informed. That’s why we rely on our team dashboard, with stats like users and hours, daily visitors, uptime, twitter feeds, and actionable items like organizations awaiting approval. One screen, all the data you need.

Originally, I had rolled our own using simple PHP and MySQL. All of our app data is in a MySQL database, why not? That worked great for quite a while, but it was time for an upgrade. In my search for creating a new dashboard, I fell in love with a (fellow 500Startups) company called Geckoboard. They allow you to easily bring in data from sources like Google Analytics, Get Satisfaction, Basecamp, Pingdom, Twitter, and more. It was perfect, a great user interface, easy to control and display, easy to setup. In less than 15 minutes, I had our dashboard up and running, looking great!

There was only one thing missing…our data! I had lots of external services setup, but nothing yet to bring in our stats from the MySQL database and into Geckoboard. This is where the story starts. Geckoboard allows for custom widgets using an api, but now I needed to get our data out of the database and into a readable format, either JSon or XML.

Turns out, a ton of people seem to want to do this, but there were no real tutorials. I had to hack together code from a bunch of different places until I finally figured out the puzzle. So, let me show you how I did it. Basically, I setup a PHP file for each widget I wanted. (I’m sure there’s a way to get them all in one xml file, feel free to comment below!)

The first step is to connect to your database, a pretty standard set of php code.

//Connect to DB
$con = mysql_connect(“loca”,”username”,”password”);

$db_selected = mysql_select_db(‘database’, $con);
if (!$db_selected) {
die (‘Can\’t use foo : ‘ . mysql_error());

if (!$con)
die(‘Could not connect: ‘ . mysql_error());

Then after that, we will want to pull the data from the database. In this case, I am pulling a count of our users to date, and for the secondary number, number of users in the last 7 days. (Geckoboard will automatically convert this to a change percentage if you choose.)

//Get Data

$users = “SELECT *, COUNT(id) FROM users”;
$users_result = mysql_query($users) or die(mysql_error());

while($users_row = mysql_fetch_array($users_result)){
$users_result_real = $users_row['COUNT(id)'];

$users_7days = “SELECT *, COUNT(id) FROM users WHERE created_at < CURDATE() – INTERVAL 7 DAY”;
$users_result_7days = mysql_query($users_7days) or die(mysql_error());

while($users_row_7 = mysql_fetch_array($users_result_7days)){
$users_result_real_7days = $users_row_7['COUNT(id)'];


And finally, create the xml file that will make this all Geckoboard available.

//Create XML Document
header( “content-type: application/xml; charset=ISO-8859-15″ );

$doc = new DOMDocument( “1.0″, “ISO-8859-15″ );



<value><?php echo $users_result_real ?></value>

<value><?php echo $users_result_real_7days ?></value>
<text>User Count 7 Days Ago</text>

Give that file a name and put it somewhere on your server. Now, if you try navigating to the file, you should see a valid xml document. You can download my entire file here. Just remember to change your database connection details and table names.

Finally, we need to add this to our Geckoboard. This step is definitely the easiest! Login to your account and add a widget. Choose the ‘Custom Widgets’ type. In this case, I used a ‘Number and Secondary Stat’ widget. You can adapt the above file to provide different data for different widget types.  Enter the url to the file on your server, choose ‘Custom’ from the widget type and ‘XML’ from the feed format. It should look something like this:

So, there you have it! A custom Geckoboard widget to pull data from your own database. Now, your stats are only limited to some MySQL skills and your imagination! Hope that helped. Feel free to leave comments with questions or improvements (note, I am not a php guy) and let us know what you think of the series. And be sure to check out Geckoboard for your dashboard setup. They even have a free trial, so why not give it a shot!


2 thoughts on “Geckoboard, PHP, and MySQL

  1. drew

    For your mysql solution you do not mention utilizing the Google graphs api. This model of aquiring data from your datasource api then, storing it to mysql sounds like best practice. The next step is to exceed geckoboards ability (and terrible graphs) with google’s javascript graph api. the graphs range from visualized conversion funnels to advanced time series line graphs that are zoom-able on demand by day, month, and year.

  2. GoVoluntr Post author

    @drew, thanks for the comment. Yes, I did not mention Google graphs api in this article as this is about implementing custom graphs into Geckoboard, not google graphs. I looked at GG before going to Geckoboard and found that the ability to a) not have to make a custom widget for everything (like uptime, twitter feeds, etc.) and b) avoiding dealing with the still young api for analytics data made it worth the small price for Geckoboard. We are also running Google Analytics (which powers almost 1/3 of our dashboard because it plugs right in) for that in-depth analytics we are looking for. The dashboard is the quick view to bring everyone to the same page.

    P.S. – I find emailing Geckoboard my feedback does wonders – great guys running the app that are totally open to feedback!

    - Kevin

Comments are closed.