This week I rewrote the SQL to calculate the win loss averages for the Snooker players. The old version using the old database design ran to 40 lines of case and if statements. It was severely limited by my database knowledge several years ago.
The new version is a dozen lines, that has a nested query to calculate the percentages from aliases that count the scores. It delivers all the information ready to be output to the screen by the HTML class. Tidy.
Last night (before midnight, I’ll have you know 
) I managed to implement a jQuery slider widget for selecting the different seasons that I have data for over the years. Out goes the old menu page leading to each section of the site, in comes a stylish, when I do the graphics, slider which I want to look like the classic snooker hall wood and brass scoreboards.
jQuery makes it so easy, an amazing free javascript library.
I’ve added more classes to the Snooker App. A news table has been added to the database too.
I also spotted a glitch with the migration data. I forgot about walkovers, well the missing player in a walkover. The migration routine had created a new player without checking if the name was empty. Ooops. I’m glad I resisted the urge to migrate all the seasons that are stored in the old database format. Fully testing one league from the 2008 season has been manageable.
The next big step will be the SQL query to work out the player’s win loss averages. Also, with the new database design, I will be able to display a lifetime average (well back to 1996 anyway 
) and many more statistics. History of wins and losses in any match will be interesting too.
All the data for displaying a full scorecard from the Snooker league app is working. Viewing the var_dump() shows all items and objects are correct.
Next job is display it. I’m thinking a AJAX/JSON call to display it as a pop-up might be nice.
Saturday was a good programming day, I almost completed all of data migration routine. Just the high breaks and match comments needed finishing today. The latest season migrated without errors… yata!
Next is the big task of the scorecard class, it’ll be a busy class.
I had a great programming run last night and didn’t finish until after 1am! The static database class is taking shape and has returned meaningful data… which is nice.
Tonight, Kaz helped me to get the results from one division in 1996 into the new database. This will allow me to write and test the league table query. With several years of flat HTML pages to work through, it’ll be a while before all the old data is processed.
In recent times it’s become apparent to me that my Snooker League site needs updating and consolidating into a full blown Object Oriented PHP application. The database design was limited by my knowledge many years ago, resulting in some horrible queries. It does the job, but is hardly a good advert for my PHP and SQL skills these days.
So today it all kicked off, a couple of draft classes were written to retrieve some results from week one of the 1996 season (the year I began making the results and league standings available on the web).
The database has 14 17 tables to date and is being updated regularly. I’m using the excellent MySQL Workbench to design and layout the tables. The database design has been work in progress for months, finally I’ve made the time to get things rolling.
Oh and it’s my birthday today too.