I love a challenge and I hate bad solutions to good problems. This often leads me into situations when I am doing things no one at the Tulsa World has done before. I had on of those moments when I looked at how we treated databases.
Up until April of 2011 all of the database work done at the Tulsa World was published through Caspio. That isn’t necessarily a bad thing. But Caspio has some limitations that I couldn’t stomach — mainly that it was ugly and difficult to use. When my editor approached me about building a couple of infographics for a higher education pay database I offered it as a chance to scrap the old Caspio system. One green light later and I was in a big mess.
An important thing to know before you read on, for various reasons — some technical, some political — I could not run a single line of server-side code in this project.
I knew from thumbing through the Google Fusion Tables API while working on maps that you could request data directly and not just as a map layer. From there, the rest of the steps in the project were purely theoretical and I had never done anything remotely that complicated before. I would have to figure out how to request a data set from Fusion Tables and digest that data into HTML markup while allowing people to make their own requests for data. And it all had to be easier to use than Caspio.
I used the Google Visualizations API to make the requests to Fusion Tables. A simple for…each loop in Javascript built the returned data into shape as an HTML table. JQuery then either inserted that HTML table into the document or overwrote the old table (if they had already made a request and were making a second one).
Half way done and I could now make requests to Fusion Tables for data and have those requests fulfilled and shown. However, the user could not make their own requests and that’s a pretty big requirement.
To solve that problem I used a generic HTML form, with a twist. An HTML form would usually send a request off to a server, but this form wouldn’t send anything because I didn’t have a server to receive it. Instead I simply pulled the values from the form using jQuery and then used those values to alter the original Fusion Tables request and send it off again to fetch the new data set.
A quick spit-shine with jQuery UI tabs and a few interesting graphs from Google’s Charts API and I was all done.