SixPitchers.com

So I finally got the site updated today, just a shade over a month after opening day. There’s still a lot I want to do with this, but given my schedule and a frustrating afternoon like today, I just need a beer.

Once I got everyone’s pitching staff updated and input (thanks to matskralc for lending a hand) I went ahead and ran my handy little Perl script to go out and grab the pitchers’ stats and then loaded those into the database. But when I went to check the site, it was only bringing up eight records and I couldn’t figure out why. Several hours later it finally dawned on me that there were a few pitchers that hadn’t been selected last year and it wasn’t doing any good to run a query to UPDATE those records that didn’t exist in the database yet. Once I figured that out, it was a pretty quick fix, but damn it was frustrating.

Maybe I’ll try to work on it some more tomorrow. Or not.

For the longest time, I’ve been perplexed about two things:

1. Why were the individual ERA calculations so screwed up
2. How could I deal with pitchers who were traded mid-season and whose stats were only being counted for his second team

The first one turned out to be an indication of my unfamiliarity with SQL. I had set up the stats database table to house innings pitched as a 2.2 decimal, incorrectly thinking that data type decimal(3,2) would allow me to store a number like 202.67. I didn’t account for the fact that the decimal point itself accounts for one of those three spaces on the left side, so any pitchers with more than 100 IP were getting truncated to 99.99 IP in the database, causing the ERA calculations to be artificially high.

For the traded pitchers part, I switched the Perl code to look at player pages (for example, Johan Santana) which has total 2006 stats instead of using the career stats page and pulling all rows with year=2006 as I’d been doing before; in the case of Jeff Weaver, I was only getting his St. Louis numbers with the old method.

Turns out both problems were easy to fix; just needed a bit of time to go in and look at the code.

I forgot to run the stat update routine this morning, so I tried it from work. At first it didn’t work so I did a little googling and found a simple line of code to incorporate proxy settings. Now, in a pinch, I can update the database from behind the firewall.

The trick is still trying to get PHP and Perl to talk to each other so I don’t have to do this semi-automatic deal in the first place. I haven’t tried tinkering with this in a while. This weekend seems like a good chance to do that.

The Perl script to harvest the data and create a SQL query to update the database is complete and working! I can now serve up current standings based on up to date statistics, but I have to run the script locally, which produces the query that I then have to upload into the database through the phpMyAdmin interface. That’s only, what - two steps? Not bad, but I really want to fully automate this so I don’t need to worry about running the update query every day.

Read the rest of this entry »

andymac brought up an interesting point about pitching matchups. I need to devise a way to track the number of times two pitchers from the same “staff” meet up, costing the team two starts for one win at best. Tomorrow’s Cubs-Cardinals game is one such game with Carlos Zambrano scheduled to start against Chris Carpenter. Three teams have both pitchers: andymac, Driver 8 and Mike D.

I got the first database-connected PHP page put together to display all the entries. A small step, but an important one. I still need to get the data-harvesting routine put together to update the stats database, but once that’s done it’s a relatively simple task to build the SQL queries and create the PHP to display everything on the web. Progress!

…it’s MySQL.

The SixPitchers.com database has been built and I even put together an amateurish-looking data model. Now all I need to do is dump a bunch of data in it then figure out how to get live stats harvested from the web in there. Hack #27 in Baseball Hacks walks through this using Perl and MySQL. How convenient! With any luck, I’ll be able to start testing this tonight.

In an effort to make the contest both more robust and easier on me (a win-win!) I’m learning how to use Perl to grab statistical data right off the web and, eventually, to save it in a MySQL database that will be used to generate on-line reports in near-real-time. In the past, I’ve had to maintain a spreadsheet with dozens of complicated lookup formulas to keep track of the contest, something that I only updated once a week at best. Once I get everything up and running, the reports will run themselves, providing daily status updates on how your pitching staff is doing.

Read the rest of this entry »

We’re taking the contest to a new level this year. Unfortunately, but predictably, I started too late to have everything up and running well in advance of the season. What you should expect to see in the coming weeks is real-time updates, stats and analysis, additional contests, and other commentary. In the meantime, stop by and register at the message board and good luck!