About This Site
Purpose
The main purpose of this site is to present the statistics from the FWL. The FWL is an on-line American Football simulation league which uses the Sierra program Front-Page Sports Football. Thanks to various programmers, notably Lorne Sundby, FPS Football can be talked into churning out masses of football stats. So, why not have all these stats available for FWL team owners? If this can be done, then the same tools can be made available to other on-line FPS Football leagues.
Process
Getting stats from FPS seems to be a pretty painless process, if you know what you're doing.
- Run Lorne Sundby's GAMESTAT program in CSV mode. This will churn out a CSV file of all the relevant stats from the current week of games. Unfortunately, GAMESTAT contains some bugs, which I might document one day.
- Write a little PHP script to take all the stats from the CSV and enter them into mySQL tables. Simple enough.
- Run Lorne Sundby's PLAY progam. This will generate scoring summaries for all the games in the current week.
- Write a little PHP script to take these files and enter the scoring plays into an mySQL table and also save all the final scores in another table.
Of course, there is always a fly in the ointment. If you forgot to run GAMESTAT following a week of games, you're snookered. If your league commissioner is clever, then they would have saved their FPS files after every week and then GAMESTAT can be used on the archived files. You just then have to keep track of which CSV file goes with which week of games. If, however, you haven't saved your files, you could well be up the creek. FPS Football does not keep stats after a week's games have finished, so I don't think there's a way to extract the stats from the program files. If you've got the play-by-play logs, you might be in luck.
Taking my lead from FP Gelat, I developed my own version of his "logger" tool. This reads a play-by-play file and generates a summary of all the plays e.g. the down and distance, who ran the ball, how many yards they gained, who made the tackle etc etc. After months of work, I now think my version of logger can deal with everything the play-by-plays can throw at it. Anyone's who has studied the play-by-plays will know how idiosyncratic they can be. My logger dumps a CSV file for each play-by-play it encounters. These are then read into a database which then summarises the stats and produces a file very similar to a GAMESTAT CSV file. Bingo - all those weeks with no stats suddenly have them.
Database Design
The main database has table to hold details of the teams and players in the league, then a separate table for each individual statistic type (rushing, passing, receiving, etc). The stats are linked to the players and the teams using foreign keys. There are also tables for the team statistics. Some team statistics tables should be redundant (for example, to get 'Team Rushing', you should be able to sum all the rushing stats for players from that team. But, GAMESTAT sometimes loses a player (perhaps if is cut immediately after a game) and the individual stats don't always sum to the team stats. So, there are tables for team rushing, team passing, etc ..., together the team stats that don't have a corresponding individual stat (first downs, time of possession, etc...)
Each entry in a stats table is also linked to the game in which the stats were accummulated. So, it becomes a trivial task to get the stats for a single game from the database. It is also simple to get each player's stats. The challenge is to work out the SQL statements required to generate the various records that go into the league record book.
Tools
Here is a quick list of tools used to build this site;
Databases
- MS Access - used to quickly develop a prototype database, test various SQL statements and to build a tool to generate stats from my logger program
- mySQL - mySQL runs all the database tasks on the webserver and a local installation was used for testing
- Navicat - navicat simply provides a graphical front-end to mySQL, allowing the user to undertake all manner of mySQL administration tasks using a nice interface.
Spreadsheets
- MS Excel - my logger program was written in Visual Basic for Applications, running inside Excel
Web-Design
- Templates for the web-pages were hand-coded using a text-editor. I used PFE.
- Style sheets were also hand-coded using PFE
- The php pags were also hand-coded using PFE before progressing onto using PHP-Ed
- I used Apache as a local webserver for testing
- Easy-PHP 1.8 was used to control Apache and mySQL on my testing machine
- I used Firefox to see what the pages looked like. Once they look nice in Firefox, I have a look in Opera, Netscape and the dreaded Internet Explorer.
Books
As a great believer in the power of the written word, highlighted below are some of the book I have consulted in order to develop this site.