scorecards (mysql) on the web!

I love cricket! For those of you unfamiliar with it, its a sport kind of like baseball. I play this local league with 3 teams generally after work and we take it pretty seriously! We like to keep scorecards of the games, store them someplace analyze them incessantly and use them to brag! Up till now these were in paper (impossible to share, maintain and aggregate).

With mobile phones theses days, its become easy to store data electronically. It was quite easy to find an Iphone App (CricMitra) that could be used for scoring and exporting the scorecards in HTML. Initially having scorecards available online was a huge thrill, but it was quickly passed over by the need to aggregate data and see statistics for the season.

This is typically done using databases and that’s where MySql comes in. Storing data in the database allows one to easily mine the data and get various statistics. Without going in details about the schema of my database, the real difficulty comes in using them comes from a) insertion of data (in our case HTML scorecards) and b) hosting this database so that it is easily accessible by webpages online.

There were typically 3 options for a novice in web like me and I tried them all. I list each below:

1) The original solution was to setup a linux server, install LAMP on the server and use this server as the website. The basic idea being to parse the HTML scorecards and store the information in the MySQL database. This database is then accessed using PHP to display contents online. I do believe that this was the best option, and I had everything setup but for the issue of a static IP. Websites, typically have static IPs (you can get around it using dynamic dns with daemon running), but my ISP blocks common ports like 80. So while I had a great website for my home network, it was useless outside!

2) To use a web hosting service. Most Web hosting services provide a LAMP setup available for users. This is really useful in setting up forms on your websites for easy storage in to databases. Here, I ran in to basic issue that the databases on the servers (MySQL) are not remotely accessible. What this means is that the databases do not accept remote connections. So I cannot connect to the database using an SQL client (I recommend Sequel Pro for Mac)  externally. Typically this is a good security design, but for my case where I had code written that parses the exported scorecard (html) and then exports data to a database, access to My SQL remotely was a must.

3) This is when I ran across db4free.net. They offer a free service of hosting MySQL databases online that can be remotely accessed. This is a test server and they do not recommend hosting production level details on the server. But for a small website, where the traffic is fairly limited, this is an ideal solution.

Currently, my database is set up in db4free.net and I have free web hosting using 000webhost. There are simple PHP pages that query the database for information and we have our own league statistics. Pretty cool and easy (provided you know a bit of coding, a bit of databases and a bit of the web).

Check it out at mplrocks.comze.com. I know that the website still needs some design, but the data is pretty cool.

The options I listed out are again in the order I feel are the best. My third option is not ideal and I would like to move to option 2, where I can use the MySQL database provided my 000webhost to store data, but that would require me to write some PHP code that could parse HTML into the database (something I am still working on 🙂 ).