Disaster

[mtvaill]

Well, NFL.com has been completely redesigned, and as a result OFFL is destroyed.  It was nice while it lasted.  Can't say I'm happy to see three years of work customizing the software for my league go to waste though.

[Brad Schwie]

Mark,

Ugh...  This doesn't sound good...  Have you looked at how drastic the changes are?  If its just a matter of changing the link locations, it might not be too bad to get it working again.

Brad

[mtvaill]

As far as I can tell, all the HTML has changed significantly.  Scores, stats, players, everything is different.  Fortunately, they don't seem to have gone the flash-everywhere route, so I think it is still possible to make OFFL work again, but it's going to take a lot of effort.  Especially with only a few weeks before the season starts.

One small silver lining, I had already patched the NFL schedule importer script to use ESPN.com instead of NFL.com (because I could never get the game times right from NFL.com), so at least that's one thing we won't have to reaccomplish.

[Brad Schwie]

Mark,

Yeah, this is the problem with scraping, I guess.  I'm not sure that ESPN will be any more secure than NFL.com, but it may be worth a shot...  Another option is to go the route that PHPFFL has gone in using an XML based approach and third party.

Or we just give up and write a patch to migrate data accumulated in OFFL to PHPFFL...

Either way is probably fine with me.  I think quite a few of us have a lot invested in OFFL, so we'd hate to see it go, but at the same time it would be prudent to part ways if the moment is right.

Brad

[mtvaill]

Okay, besides players.php, games.php and processstats.php, what other files reach out and touch NFL.com?  Anything?

[Brad Schwie]

Thats everything I know about...

I didn't see a lot of flash on the NFL's revised site, but I didn't look too hard.  Are you thinking about taking a stab at fixing the code?

[mtvaill]

Yes.  I am creating a couple yahoo leagues in the interim to keep my guys going, but I plan on returning to offl as soon as possible.  Like you said... too much time invested :)

Some of the things I've added over the past three years:
Free Agent points system
Injured Reserve system
Trades now can include draft picks and free agent points as well as multiple players
Timeline tracking system to better control the flow of events - helps make it dynasty league friendly
Team colors customizations
Hall O' Fame / Player Records for career stats
User/franchise history, head to head stats, etc
Sortable season league leaders stats
Begun work on online bylaws display/editor
...and a few other things I can't remember off the top of my head.

[mtvaill]

Okay, I've written a driver to update player profiles in the players table, add new players and warn you which player names appear more than once in your db so you can manually edit them.  Play with the commented sections to see what the script will do, then uncomment line 195 to make it happen once you're ready.  Good luck!

<?php

require_once("offlconfig.php");
require_once($DOC_ROOT . "/lib/header.php");

        $x = new OFFL_NFLTeam();
        $teams = $x->getAllNFLTeams();
        $x = new OFFL_Position();
        $positions = $x->getAllPositions();
        $pos_id = Array();
        $pos_name = Array();

        foreach ($positions as $position)
        {
            $pos_id[$position->getPositionAbbv()] = $position->getPositionID();
            $pos_name[$position->getPositionID()] = $position->getPositionAbbv();
        }

        $warnings = array();
        $playersmoved = array();
        $sqllist = Array();
        $sqllist[0] = "UPDATE players SET active=0 WHERE position_id<>8"; // not defense
        $num = 0;   

        // here's the main team iterative loop
        foreach($teams as $team)
        {
            $player = array();
            $nflteamcity = $team->getNFLTeamCity();
            $nflteamname = $team->getNFLTeamName();
            $nflteamabbv = $team->getNFLTeamAbbv();
            $nflteamfullname = str_replace(" ", "", strtolower($nflteamcity . $nflteamname));

            $teamurl = "http://www.nfl.com/teams/" . $nflteamfullname . "/roster?team=" . $nflteamabbv;

            $nflteam_id = $team->getNFLTeamID();

            $teamroster = file("$teamurl");
            $teamstring = "";

            foreach ($teamroster as $teamline)
            {
                $teamstring .= $teamline;
            }

            list($beforetable, $tableandafter) = explode("<tbody>", $teamstring, 2);
            unset($beforetable);
            unset($teamstring);
            list($table, $aftertable) = explode("</tbody>", $tableandafter, 2);
            unset($aftertable);
            unset($tableandafter);

            $players_raw = explode("<tr ", $table);

            for ($i = 1; $i < sizeof($players_raw); $i++)
            {
                // structure of the old player object:
                    // $player[$i][0] last name
                    // $player[$i][1] first name
                    // $player[$i][2] position abbv
                    // $player[$i][3] profile relative url
                    // $player[$i][4] trash, but I'll use this to store nflteam_id
                       
                //$player_temp = explode("<td", $players_raw[$i], 5); don't need five parts in the new format
                list ($junk, $player_info) = explode("<td style=\"text-align:left\"> ", $players_raw[$i], 2);
                list ($player_info, $player_pos, $rest) = explode("</td>", $player_info, 3);

                // here's what we should have now:
                // $player_info should contain : <a href="/players/ericalexander/profile?id=ALE241391">Alexander, Eric</a>
                // $player_pos  should contain : <td>LB
                list ($junk, $player[$i][3], $player_name) = explode("\"", $player_info, 3);
                list ($junk, $player[$i][2]) = explode(">", $player_pos, 2); // done position

                // should give us the following:
                // $player_url  : /players/ericalexander/profile?id=ALE241391
                // $player_name : >Alexander, Eric</a>
                list ($player_lname, $player_fname) = explode(", ", $player_name, 2);
                list ($junk, $player[$i][0]) = explode(">", $player_lname, 2); // done last name
                list ($player[$i][1], $junk) = explode("<", $player_fname, 2); // done first name

                if ($player[$i][3] != "") // should fix finding players w/o profiles.
                    $player[$i][3] = "http://www.nfl.com" . $player[$i][3]; // done profile

                $player[$i][4]=""; // blank this out to use later for nflteam_id
                // get rid of the leading space on player first names
                $player[$i][1] = ltrim($player[$i][1]);

                $player[$i][0] = mysql_escape_string($player[$i][0]); // last name
                $player[$i][1] = mysql_escape_string($player[$i][1]); // first name

                if (strcmp($player[$i][2], "FB") == 0)
                    $player[$i][2] = "RB";

                foreach ($pos_name as $pos)
                {
                    if (strcmp($player[$i][2], $pos) == 0)
                    {
                        $player[$i][2] = $pos_id[$pos];
                        break;
                    }
                }
            }

            // test
            // echo "<table>";
            // echo "<tr><th colspan=\"4\">Offense players for team " . $nflteamfullname . " " . $team->getNFLTeamID() . "</th></tr>";

            foreach ($player AS $i=>$plr)
            {

                if(is_numeric($player[$i][2]))
                {
                    $sql = "SELECT player_id, nflteam_id, fname, lname, position_id, nfl_profile FROM players WHERE lname = '" . $player[$i][0] . "' AND fname = '" . $player[$i][1] . "'"; //nflteam_id = " . $team->getNFLTeamID() . " AND
                    $result = mysql_query($sql) or die (mysql_error() . ": $sql");//,$this->_conn
                    $multi = mysql_num_rows($result);
                    if($multi > 1)
                    {
                        $warnings[]= "<tr><td colspan=\"8\">Warning, multiple players by this name: " . $player[$i][1] . " " . $player[$i][0] . "</td></tr>";
                    /*    echo "<tr>";
                        echo "<td>" . $player[$i][1] . "</td><td>" . $player[$i][0] . "</td><td>" . $player[$i][2] . "</td><td>" . $player[$i][3] . "</td>";
                        echo "<td colspan=\"8\">***********See Warnings below***********</td>";
                        echo "</tr>";    */
                    }
                    else
                    {   
                    /*    if($team->getNFLTeamID() != mysql_result($result,0,"nflteam_id") && mysql_result($result,0,"nflteam_id") != "" && mysql_result($result,0,"nflteam_id") != 0)
                        {
                            $playersmoved[] = "<tr><td colspan=\"8\">Warning, Player not on expected team: " . $player[$i][1] . " " . $player[$i][0] . " On team " . $team->getNFLTeamID() . " found on team " . mysql_result($result,0,"nflteam_id") . "</td></tr>";
                        }
                        echo "<tr>";
                        echo "<td>" . $player[$i][1] . "</td><td>" . $player[$i][0] . "</td><td>" . $player[$i][2] . "</td><td>" . $player[$i][3] . "</td>";
                        echo "<td>" . mysql_result($result,0,"fname") . " " . mysql_result($result,0,"lname") . "</td><td>Team " . mysql_result($result,0,"nflteam_id") . "</td><td>" . mysql_result($result,0,"position_id") . "</td><td>" . mysql_result($result,0,"nfl_profile") . "</td>";
                        echo "</tr>";
                    */
                        if(mysql_result($result,0,"player_id") != "")
                        {    $sqllist[] = "UPDATE players SET active=1, nflteam_id=" . $team->getNFLTeamID() . ", nfl_profile='" . $player[$i][3] . "' WHERE player_id=" . mysql_result($result,0,"player_id");    }
                        else
                        {    $sqllist[] = "INSERT INTO players (lname,fname,nflteam_id,position_id,nfl_profile) VALUES (" . $player[$i][0] . "," . $player[$i][1] . "," . $team->getNFLTeamID() . "," . $player[$i][2] . ",'" . $player[$i][3] . "')";    }
                    }
                    $num++;
                }
                else
                {
                    unset($player[$i]);
                }
            }
            // echo "<tr><td colspan=\"8\">" . $num . "</td></tr>";
            // echo "</table>";
           
                       

        }
        if(sizeof($warnings))
        {
            echo "<table>";
            echo "<tr><td>Following players not updated; handle these players manually:</td></tr>";
            foreach($warnings AS $warning)
            {
                echo $warning;
            }
            echo "</table>";
        }
/*
        if(sizeof($playersmoved))
        {
            echo "<table>";
            echo "<tr><td>Following players not updated; handle these players manually:</td></tr>";
            foreach($playersmoved AS $warning)
            {
                echo $warning;
            }
            echo "</table>";
        }
*/
        // $sqllist[] = "UPDATE players SET nflteam_id=0 WHERE active=0";

        // The SQL statements are prepared.    Now open the database and run them.

        $db = new OFFL_DBObject();
        $dbconn = $db->getConn();

        foreach ($sqllist as $sql)
        {    //$result = mysql_query($sql, $dbconn);
            echo "$sql<br>\n";
        }
           
        $db->closeDB();

        echo "<h2>Player update complete!</h2>";
               
?>

[Brad Schwie]

Mark,

Nice work on this code.  It worked just fine for me.

As my league uses an auction format, I've already updated my player profiles with your code and will use OFFL for tracking how much each team has spent, but I think I'm going to use PHPFFL for the rest of the season.

Thanks again for submitting this.  You're making our auction operate smoothly.

Brad

[Brad Schwie]

To follow up my previous post about mtvaill's script, I would discourage anyone else from running this script unless they have the players table backed up before doing so.  I believed I had updated my database using this script, but in reality, I it only somewhat worked...

In addition, running this script without a viable backup will probably render your previous player ID's useless with the patch we plan to introduce for allowing OFFL to work for the 2007 season with CBS Sportsline.  Thanks to Russ and Jamie on making this possible!

Brad

[mtvaill]

I'm glad it helped someone.  I'm going with phpffl as well; I've already put up a couple mods on the forums there; look for posts by me (mt-vaill, there). 

Peace,
Mark

[JamieK]

Hello Brad and mtvaill. I'm encouraged by your efforts so far on fixing OFFL, but a bit discouraged that you are going to phpffl. It is definitely understandable though. This will be the third year (or was going to be the third year) that my league is going to use OFFL. I can't say that I'm quite ready to give up on OFFL, at least for this year anyway. My league just won't want to lose our historical data and such. I have begun looking in to phpffl and I want to test it. I am still considering my options. I am a bit concerned about phpffl's use of myffl.net as the stat source at this point as well, considering the message posted at the home page (although I read in a forum the data stream still works??). mtvaill, I see your player profile update code and I am going to try it out. Thank you for sharing that. I am seriously thinking about working on the stats and schedule scraping within OFFL for the new NFL site. If you two happen to have any more insight you might be able to offer I would much appreciate it. OFFL has worked very well for my league. At the very least I think I want to get my league by on OFFL for this year.

What do you think of PHPFFL?

Thanks for anything you can offer. I just can't decide what to do at this point.

[Brad Schwie]

Stratego,

Thanks for the note.

I've installed phpFFL and so far, I'm a little surprised and disappointed at the number of bugs in it and the limitations it has.  I was under the impression that it was much more mature, but in its current state, I think OFFL requires much less help from a league administrator.  I've already seen  issues where players were on 2 rosters at once, waivers don't automatically process if no one is dropped in the same transaction, there is no cap to the roster, etc...  Its become such a headache, my league commissioner is contemplating abandoning everything and moving to ESPN.

The only real issue remaining with OFFL, for this coming season, appears to be the stat scraping, and unfortunately, this appears to be a significant hurdle.  Mark and Russ have spent some time looking at the nfl.com code and they found issues with trying to get it to work with OFFL again (the player id's seem to change from game to game).  Russ mentioned that CBS Sportsline might be the best way to scrape stats this year, as it employs the format that nfl.com used last year...  In addition, I know that myffl.net is scraping their stats from CBS Sportsline as well...  If you attempt to fix the stat scraping in OFFL, I would definitely modify it to scrape CBS Sportsline instead (I believe Russ said that this could be as easy as a find/replace for nfl.com to sportsline.com)

My league and I are torn at what package we should use this year.  People in my league prefer the reliability, inferface, and transaction reporting abilities of OFFL, but the lack of stat scraping at the moment is a real deal breaker.

What are your thoughts?

Brad

[JamieK]

mtvaill, I see your scheduling code which utilizes espn. Thank you for that in addition to other work you have done. Do you happen to know if anyone has done any work on stat scraping from NFL? If not I may consider working on that.

Does anyone see any reason to not use OFFL this year if these three pieces are working (player profiles, schedules, stats)?

Thanks in advance for any opinions, ideas, suggestions, or help you can offer :)

[JamieK]

Brad,

I appreciate your response. It sounds like I want to stay away from phpFFL at this point. That means I will need to work on OFFL stat scraping from Sportsline. I am gasping at the fact that nfl.com is changing player IDs! I do have something on my side, and that is time (although even that is dwindling). My new job doesn't start until the 10th, I am leaving town for 2 days this weekend, and that leaves me about 6 days to work on it. I'll just say goodbye to my life for a while. Although 6 days *should* be plenty of time.

Thanks for your insight Brad. If anyone else happens to have Sportsline scraping accomplished already, would you mind sharing? :)

Jamie (stratego)

[Brad Schwie]

Jamie,

Do you have your sourceforge email forwarding set up?  If so, I'll email you some information/conclusions that Russ and Mark came up with.

Brad

[JamieK]

Brad,

Yes my Email forward is set up in SourceForge.

Thank you!

[mtvaill]

Guys,

I simply don't have the time right now to put into fixing OFFL before the season starts; I have an overseas business trip coming up in a month, plus family concerns are keeping me busy as the season is right around the corner.  I had started working on scraping ESPN for stats, but looking into their player id system, they have the same issues as many other sites (Fox, USAToday, to name a couple) with multiple entry points into their player/stat system.  It sounds encouraging that the CBS site still has the old format they shared with nfl.com, but now that they aren't shackled to the NFL who knows what they will do.  phpFFL may not be perfect (yet!) but they do have an active community and responsive developers, so that seems encouraging.  Safety in numbers, I guess.  I'm looking into getting on the phpFFL development team, actually, so maybe after I get back I'll be able to lend them a hand expanding the software.

I put a lot of time into OFFL, and it was a great learning experience, but since my code has been diverging significantly from OFFL baseline, I've been feeling more and more like an island.  NFL.com pulling the rug out has opened my eyes, I guess.  I hope you guys will reconsider phpFFL if the stat scraping doesn't get fixed in OFFL.
Sorry I can't be more help,
Mark

[vikesfan]

Let's not give up so quickly.  I just sent Brad some code to review.  It's a dump of the CBS Sportline game stat page only right now but it demonstrates this is doable.  Mainly the part of reading the NFL schedule from the DB to get the links to scrape and then taking the data and updating the DB are what's left.  Both of those pieces exist in the current application they just need to be merged with the code here.  The nice thing is this uses the existing NFL player ID's as prior years (assuming the players scrape, see below) is still done.

[Brad Schwie]

Russ,

I sent you an email about this.  I'm waiting to hear from Jamie before I decide if I want to move forward with your code, but thanks for submitting this!  I think all of us agree that myffl.net is the way to go and Jamie is looking at getting it to work with OFFL.  Hopefully we have a couple options brewing right now, as I really want to get OFFL working again this year and for the future.

Brad

[jmharms]

I'm starting fresh with the OFFL site and I'm having trouble getting the script above to work for me.  I'm looking to use OFFL to start with by running our auction tomorrow night.  Would it be possible for someone to export the players table and any other associated tables so that I could import to get ready for my auction on Tuesday??

Feel free to PM me with the file(s).  I'd also be interested in doing some testing of your changes to keep the development progressing.  I really like what you guys have done with the OFFL site.

Thanks again.

Jason.

[Brad Schwie]

Jason,

I sent you a message (with the player dump file) to your sourceforge user account via email, but you must not have mail forwarding set up, because it bounced back at me.

Brad