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.

<?php
//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.)

<?php
//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_row['COUNT(id)'];
$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_row_7['COUNT(id)'];
$users_result_real_7days = $users_row_7['COUNT(id)'];
}

?>

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

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

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

?>

<root>

<item>
<value><?php echo $users_result_real ?></value>
<text>Users</text>
</item>

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

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!

Kevin