DBUpdater 2 For FOF7

[Ben E Lou]

LINK: http://www.younglifenorthdekalb.com/fof7downloads/dbupdaterv2.0.4.zip

I've finished a web-based database updater for FOF7 and I think it's release-ready. The php application opens the FOF7-generated csvs of your choosing (they must first be uploaded via FTP to the same web directory as your dbupdater.php file), reads them, updates--depending on how many csvs you upload--up to 25 database tables, and removes the csvs from your server.

It is not as user-friendly as Greg's great utility was. Greg is a pro, and I'm a rank amateur. It'll likely never come close to being as user-friendly, but it works (I think.) ;) Usage will require you to make some decisions and upload some csvs at the right times during the season. Over time, I may make it "smarter" in terms of figuring out if particular tables need to be updated, but for now, it's up to the user to upload the csvs that he wants to update.

FILES INCLUDED
dbupdater.php--Php file that processes the csvs and updates the database.
dbcreate.sql--Creates the initial blank database.

INITIAL SETUP
1. Create a blank MySQL database.
2. You'll need to run the MySQL script (dbcreate.sql) on your blank database. It will create the tables and format them in the manner DBUpdater 2.0 expects.
3. Open dbupdater.php with a text editor and enter the values for your database host (usually localhost), username, password, and database name in the appropriate slots near the beginning of the script.

USAGE NOTES
You must upload universe_info.csv via FTP for the files to be processed. DBUpdater needs to read the current stage. If universe_info.csv does not exist in the same directory as dbupdater.php, you'll get an immediate error message and the application will exit.
The current year is required in the url to run DBUpdater: {yourservername}/{directoryname}/dbupdater.php?year={currentyear}. In other words, if I were to run this for the WOOF, currently in 2013 the url might be something like http://www.fof-woof.com/csvs/dbupdater.php?year=2013. (Hint: just bookmark that and you can use the same url all season long, then just change the bookmark next season.)
It's a waste of time to upload every new csv every time you run DBUpdater. Which ones you'll want to upload will partially depend on what you're actually using. For a long-running league, the _records.csv files can get very large, and most of them do not change from week to week. I'd generally recommend as follows:
PRESEASON
universe_info.csv
game_information.csv
player_record.csv
transactions_{yyyy}.csv
fof7_active_quarterbacks.csv (You need do this one only once per preseason, to capture the new formations learned in TC.)
REGULAR SEASON
universe_info.csv
game_information.csv
player_record.csv
transactions_{yyyy}.csv
standings.csv
staff_history.csv
team_information.csv
team_statistics.csv
team_vs_team.csv
player_information.csv (This one is non-critical, but if you're reporting the QB Wins/Losses/Ties, that data does change from week to week. If not, then just update this one at the beginning of each offseason and after the draft class is generated, and you'll be fine.)
game_records.csv (This one is very large in older leagues. If you're not using it, then don't bother.)
POSTSEASON
universe_info.csv
game_information.csv
player_record.csv
transactions_{yyyy}.csv
staff_history.csv
team_information.csv
team_vs_team.csv
player_information.csv (See note above.)
career_records.csv (Update once per postseason. This csv file is only updated once per season, after Week 17.)
season_records.csv (Update once per postseason. This csv file is only updated once per season, after Week 17.)
fof7_team_season_records.csv
playoff_game_records.csv
team_playoff_game_records.csv
OFFSEASON
universe_info.csv
player_record.csv
transactions_{yyyy}.csv
rookies.csv (Once per offseason, after the rookie class is generated.)
staff.csv (Twice per offseason, immediately before and after the staff draft.)
team_information.csv (It doesn't change every stage, but it's so small, might as well just upload it every time since it does change for most of them.)
player_information.csv (Update this one at the beginning of each offseason and after the draft class is generated. No need to do it more frequently than that as far as I can tell.)
awards.csv (Once per offseason. I'd recommend doing it in the very first offseason stage. I wait until offseason rather than post-bowl to do this so that it captures Legend of The Game and Coach Of The Year.)
team_yearly_information.csv (Once per offseason. Should probably be updated with the first export of the new season.) ADDITIONAL STUFF
Here are a few things that it will do outside of just grabbing the csvs and uploading to the database as-is...

Writes position_id and positiongroup_id to the player_information table so those don't have to be looked up or joined as text fields.
Creates a unique id field for several tables that don't have one.
If you combine all of the transactions_{year}.csv files or player_season_{year}.csv into transactions all.csv and/or player_season_all.csv, it will update those for the entire FOF2K7/FOF7 history of the league. (I recommend each of these as a solo import, as they take a good bit of time if your league has a lot of history.)
If you are running this for the first time on a league that is past its initial season, you're going to need to rename the following files so that DBUpdater knows to write everything in them and not just the current season:
game_information.csv-->game_information_all.csv, standings.csv-->standings_all.csv, staff_history.csv-->staff_history_all.csv and team_statistics.csv-->team_statistics_all.csv
Creates and updates a fof7_gameinfo table that holds the current season, stage number, salary cap, and minisal. (That's one reason that universe_info.csv is required for every upload.)
Initial setup schema file includes fof7_stageinfo
Again, I'm an amateur. There are places where the code is just plain ugly. If you can't handle ugly code, I suggest you not peek at it. ;)
Let me know if you have questions.




PLEASE NOTE: If you need help, please post in this thread. I may not be available immediately, and someone else might be able to assist you with your question. Please do not PM, email, or IM me for one-on-one support on this unless I specifically request it. All unsolicited private support requests regarding this utility will be ignored.

[Dutch]

Awesome stuff. Good job, Ben!

[WheelsVT]

Awesome stuff Ben. Enabling the next generation of great league sites.

[PackerFanatic]

Very, very nice. Good work Ben!

[3ric]

Hm, I get
"SELECT week FROM fof7translate_season_weeks where stage=""
Table 'hflfof7.fof7translate_season_weeks' doesn't exist"

There's no create script for that table in the dbcreate.sql file from what I can see.

[Ben E Lou]

Oops. Here it is. I'll add it to the file in a bit:

CREATE TABLE IF NOT EXISTS `fof7translate_season_weeks` (
`Week` varchar(2) DEFAULT NULL,
`Stage` varchar(19) DEFAULT NULL,
UNIQUE KEY `Week_3` (`Week`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `fof7translate_season_weeks`
--

INSERT INTO `fof7translate_season_weeks` VALUES('1', 'Ex. Season Week 1');
INSERT INTO `fof7translate_season_weeks` VALUES('2', 'Ex. Season Week 2');
INSERT INTO `fof7translate_season_weeks` VALUES('3', 'Ex. Season Week 3');
INSERT INTO `fof7translate_season_weeks` VALUES('4', 'Ex. Season Week 4');
INSERT INTO `fof7translate_season_weeks` VALUES('5', 'Ex. Season Week 5');
INSERT INTO `fof7translate_season_weeks` VALUES('6', 'Reg. Season Week 1');
INSERT INTO `fof7translate_season_weeks` VALUES('7', 'Reg. Season Week 2');
INSERT INTO `fof7translate_season_weeks` VALUES('8', 'Reg. Season Week 3');
INSERT INTO `fof7translate_season_weeks` VALUES('9', 'Reg. Season Week 4');
INSERT INTO `fof7translate_season_weeks` VALUES('10', 'Reg. Season Week 5');
INSERT INTO `fof7translate_season_weeks` VALUES('11', 'Reg. Season Week 6');
INSERT INTO `fof7translate_season_weeks` VALUES('12', 'Reg. Season Week 7');
INSERT INTO `fof7translate_season_weeks` VALUES('13', 'Reg. Season Week 8');
INSERT INTO `fof7translate_season_weeks` VALUES('14', 'Reg. Season Week 9');
INSERT INTO `fof7translate_season_weeks` VALUES('15', 'Reg. Season Week 10');
INSERT INTO `fof7translate_season_weeks` VALUES('16', 'Reg. Season Week 11');
INSERT INTO `fof7translate_season_weeks` VALUES('17', 'Reg. Season Week 12');
INSERT INTO `fof7translate_season_weeks` VALUES('18', 'Reg. Season Week 13');
INSERT INTO `fof7translate_season_weeks` VALUES('19', 'Reg. Season Week 14');
INSERT INTO `fof7translate_season_weeks` VALUES('20', 'Reg. Season Week 15');
INSERT INTO `fof7translate_season_weeks` VALUES('21', 'Reg. Season Week 16');
INSERT INTO `fof7translate_season_weeks` VALUES('22', 'Reg. Season Week 17');
INSERT INTO `fof7translate_season_weeks` VALUES('23', 'Wild Card Round');
INSERT INTO `fof7translate_season_weeks` VALUES('24', 'Divisional Round');
INSERT INTO `fof7translate_season_weeks` VALUES('25', 'Conference Round');
INSERT INTO `fof7translate_season_weeks` VALUES('26', 'End of Season');

[3ric]

Thanks!

Looking good now,
2032 rows inserted into fof7_awards in 2 seconds.
194 rows inserted into fof7_active_quarterbacks in 0 seconds.
2 rows inserted into fof7_active_free_agency in 0 seconds.
78852 rows inserted into fof7_career_records in 46 seconds.
16 rows inserted into fof7_game_information in 1 seconds.

[gstelmack]

We'll make a programmer out of you yet!

[3ric]

Next issue...

116248 rows inserted into fof7_game_records in 79 seconds.
Table 'hflfof7.fof_mappings' doesn't exist
SELECT id FROM fof_mappings m WHERE m.position='QB'

[Ben E Lou]

Ahhhhh...that's an old Stelmack table that I'm using. I overlooked that new databases wouldn't have it.

-- phpMyAdmin SQL Dump
-- version 4.1.4
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Mar 07, 2014 at 01:39 PM
-- Server version: 5.6.15-log
-- PHP Version: 5.5.8

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `ccfl`
--

-- --------------------------------------------------------

--
-- Table structure for table `fof_mappings`
--

CREATE TABLE IF NOT EXISTS `fof_mappings` (
`ID` smallint(5) unsigned NOT NULL DEFAULT '0',
`Position` char(4) NOT NULL DEFAULT '',
`PositionGroup` char(3) NOT NULL DEFAULT '',
`TransactionType` char(64) NOT NULL DEFAULT '',
`Precipitation` char(10) NOT NULL DEFAULT '',
`PlayerStatus` char(20) NOT NULL DEFAULT '',
`StaffRole` char(20) NOT NULL DEFAULT '',
`Playoffs` char(20) NOT NULL DEFAULT '',
`Ability` char(12) NOT NULL DEFAULT '',
`StadiumType` char(25) NOT NULL DEFAULT '',
`ConstructionType` char(15) NOT NULL DEFAULT '',
`DriveResult` char(15) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

--
-- Dumping data for table `fof_mappings`
--

INSERT INTO `fof_mappings` VALUES(0, 'NA', 'NA', 'None', 'Fair', 'Active', 'Head Coach', 'None', 'Poor', 'Outdoor/Grass', 'None', 'None');
INSERT INTO `fof_mappings` VALUES(1, 'QB', 'QB', 'Signed as free agent', 'Rain', 'Injured Reserve', 'Off Coord', 'Wildcard Round', 'Poor', 'Outdoor/Turf', 'Renovation', 'Punt');
INSERT INTO `fof_mappings` VALUES(2, 'RB', 'RB', 'Resigned as an unrestricted free agent', 'Stormy', 'Inactive', 'Def Coord', 'Divisional Round', 'Poor', 'Dome/Turf', 'New Stadium', 'Downs');
INSERT INTO `fof_mappings` VALUES(3, 'FB', 'FB', 'Released', 'Snow', 'Suspended', 'None', 'Conference Round', 'Poor', 'Retractable Roof/Grass', '', 'Half');
INSERT INTO `fof_mappings` VALUES(4, 'TE', 'TE', 'Received player in trade from', '', '', '', 'Conference Champion', 'Poor', '', '', 'Game');
INSERT INTO `fof_mappings` VALUES(5, 'FL', 'WR', 'Received draft pick in trade from', '', '', '', 'League Champion', 'Poor', '', '', 'Fumble');
INSERT INTO `fof_mappings` VALUES(6, 'SE', 'C', 'Retired', '', '', '', '', 'Poor', '', '', 'Interception');
INSERT INTO `fof_mappings` VALUES(7, 'LT', 'G', 'Designated franchise player', '', '', '', '', 'Poor', '', '', 'Safety');
INSERT INTO `fof_mappings` VALUES(8, 'LG', 'T', 'Signed to a new contract', '', '', '', '', 'Poor', '', '', 'FieldGoal');
INSERT INTO `fof_mappings` VALUES(9, 'C', 'P', 'Placed on injured reserve', '', '', '', '', 'Poor', '', '', 'Touchdown');
INSERT INTO `fof_mappings` VALUES(10, 'RG', 'K', 'Suspended for off-field conduct', '', '', '', '', 'Poor', '', '', 'MissedFG');
INSERT INTO `fof_mappings` VALUES(11, 'RT', 'DE', 'Hired as head coach', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(12, 'P', 'DT', 'Hired as lead scout', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(13, 'K', 'ILB', 'Signed to franchise player contract', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(14, 'LDE', 'OLB', 'Franchise player released under cap rule', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(15, 'LDT', 'CB', 'Signed as an unrestricted free agent from', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(16, 'NT', 'S', 'Signed a renegotiated contract', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(17, 'RDT', 'LS', 'Lost draft pick for cap violation', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(18, 'RDE', '', 'Hired as offensive coordinator', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(19, 'SLB', '', 'Hired as defensive coordinator', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(20, 'SILB', '', 'Has begun a contract holdout', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(21, 'MLB', '', 'Has ended his contract holdout', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(22, 'WILB', '', 'Will re-enter the draft', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(23, 'WLB', '', 'Turned down a contract offer', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(24, 'LCB', '', 'Offer withdrawn due to lack of cap room', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(25, 'RCB', '', 'Signed his rookie contract', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(26, 'SS', '', 'Changed primary position to', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(27, 'FS', '', 'Sent to play in summer league', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(28, 'LS', '', 'Injury', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(29, '', '', 'Renovation Vote', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(30, '', '', 'Construction Vote', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(31, '', '', 'Franchise Move', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(32, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(33, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(34, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(35, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(36, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(37, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(38, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(39, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(40, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(41, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(42, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(43, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(44, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(45, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(46, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(47, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(48, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(49, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(50, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(51, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(52, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(53, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(54, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(55, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(56, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(57, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(58, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(59, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(60, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(61, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(62, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(63, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(64, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(65, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(66, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(67, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(68, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(69, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(70, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(71, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(72, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(73, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(74, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(75, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(76, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(77, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(78, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(79, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(80, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(81, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(82, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(83, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(84, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(85, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(86, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(87, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(88, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(89, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(90, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(91, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(92, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(93, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(94, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(95, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(96, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(97, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(98, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(99, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(100, '', '', '', '', '', '', '', 'Excellent', '', '', '');

[Ben E Lou]

(Oh, and that table has been modified to include LS.)

[Ben E Lou]

2.0.1 released with scripts to create those two tables. Link in first post updated.

[3ric]

Same thing with
Table 'hflfof7.fof_positions' doesn't exist
select p.positiongroup FROM fof_mappings m JOIN fof_positions p on p.position=m.id WHERE m.position='QB'

I assume it should be

CREATE TABLE IF NOT EXISTS `fof_positions` (
`position` tinyint(2) NOT NULL DEFAULT '0',
`positiongroup` tinyint(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`position`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `fof_positions`
--

INSERT INTO `fof_positions` VALUES(1, 1);
INSERT INTO `fof_positions` VALUES(2, 2);
INSERT INTO `fof_positions` VALUES(3, 3);
INSERT INTO `fof_positions` VALUES(4, 4);
INSERT INTO `fof_positions` VALUES(5, 5);
INSERT INTO `fof_positions` VALUES(6, 5);
INSERT INTO `fof_positions` VALUES(7, 8);
INSERT INTO `fof_positions` VALUES(8, 7);
INSERT INTO `fof_positions` VALUES(9, 6);
INSERT INTO `fof_positions` VALUES(10, 7);
INSERT INTO `fof_positions` VALUES(11, 8);
INSERT INTO `fof_positions` VALUES(12, 9);
INSERT INTO `fof_positions` VALUES(13, 10);
INSERT INTO `fof_positions` VALUES(14, 11);
INSERT INTO `fof_positions` VALUES(15, 12);
INSERT INTO `fof_positions` VALUES(16, 12);
INSERT INTO `fof_positions` VALUES(17, 12);
INSERT INTO `fof_positions` VALUES(18, 11);
INSERT INTO `fof_positions` VALUES(19, 14);
INSERT INTO `fof_positions` VALUES(20, 13);
INSERT INTO `fof_positions` VALUES(21, 13);
INSERT INTO `fof_positions` VALUES(22, 13);
INSERT INTO `fof_positions` VALUES(23, 14);
INSERT INTO `fof_positions` VALUES(24, 15);
INSERT INTO `fof_positions` VALUES(25, 15);
INSERT INTO `fof_positions` VALUES(26, 16);
INSERT INTO `fof_positions` VALUES(27, 16);
INSERT INTO `fof_positions` VALUES(28, 17);

[3ric]

OK, this error is related to my data this time, the player has a nickname which breaks the SQL syntax

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Pignose"","QB","26","0","18","5","73","205","0","0","0","0","1950","1","1","71",' at line 1
INSERT INTO fof7_player_information VALUES("3856","Plunkett","Jim","0","Jim "Pignose"","QB","26","0","18","5","73","205","0","0","0","0","1950","1","1","71","4","25","22","1971","11","31","19","0","0","0","0","10","1","1")

[Ben E Lou]

Yup. That's the table. I will add that to the zip file later this morning. On the other issue, yeah, it isn't going to handle quotation marks within a nickname field. I would just change the nickname.

[Ben E Lou]

Have you run across any other missing tables? I'd like to include those on next upload this morning. I didn't think to test this on a brand-new database. All of the leagues that I am using it on already had FOF 2007 databases.

[3ric]

This error was a little more difficult.
Column count doesn't match value count at row 1
INSERT INTO fof7_player_information VALUES("35388","Flannery","Raymond","0","","LS","0","0","0","0","73","272","0","0","0","0","1980","10","15","37","0","0","0","0","0","0","0","0","7635","30","0","1","28")

I think it's because he's a long snapper, the fault is there's a missing line in fof_positions matching values 28 and 17... trying that now

[3ric]

Yes, that was it...
11606 rows inserted into fof7_player_information in 26 seconds.
2868 rows inserted into fof7_player_record in 4 seconds.
5733 rows inserted into fof7_player_ratings_season in 4 seconds.
0 rows inserted into fof7_standings in 0 seconds.
53656 rows inserted into fof7_playoff_game_records in 37 seconds.
908 rows inserted into fof7_rookies in 0 seconds.

[3ric]

Have you run across any other missing tables? I'd like to include those on next upload this morning. I didn't think to test this on a brand-new database. All of the leagues that I am using it on already had FOF 2007 databases.

No, I'm updating as I progress through the script... should be done soon

[3ric]

Twice now the script has just stopped, I assume it's a timeout preventing queries going on for a long time. I'll just resume by uploading the universe file again.

[3ric]

98865 rows inserted into fof7_season_records in 105 seconds.
485 rows inserted into fof7_staff in 1 seconds.
160 rows inserted into fof7_staff_history in 0 seconds.
33001 rows inserted into fof7_team_game_records in 31 seconds.
33 rows inserted into fof7_team_information in 0 seconds.
7796 rows inserted into fof7_team_playoff_game_records in 7 seconds.
10921 rows inserted into fof7_team_season_records in 10 seconds.
64 rows inserted into fof7_team_statistics in 1 seconds.
993 rows inserted into fof7_team_vs_team in 0 seconds.
1839 rows inserted into fof7_transactions in 2 seconds.


TOTAL SCRIPT RUN TIME: 2:37.

NEXT STAGE: 2005 Reg. Season Week 4

[Ben E Lou]

Twice now the script has just stopped, I assume it's a timeout preventing queries going on for a long time. I'll just resume by uploading the universe file again.Hmmm...I never saw that one in testing, even when I ran every single file through it. Maybe it's a server setting that's timing it out. It ran for more than 8 minutes and completed when I did the full IHOF player_season_all update.

On fof_positions, yeah, same thing as fof_mappings: I updated those probably within the first few days after the FOF7 release to account for long snappers. It was so long ago that I forgot that I'd done it. I'll edit your post with that script and add it to the main zip as well. (It'll still be called 2.0.1 since it's so close after the first 2.0.1.)

[Ben E Lou]

OK. Post edited and new 2.0.1 zip file uploaded. I should have thought to test this on a blank db before I released it, but I think we're good now. Thanks for your patience, 3ric.

[3ric]

Hmmm...I never saw that one in testing, even when I ran every single file through it. Maybe it's a server setting that's timing it out. It ran for more than 8 minutes and completed when I did the full IHOF player_season_all update.


It's going to vary between the individual server hosts what the timeout setting is, mine is stricter than yours and that's probably hard to tell before you've tried it - could be worth mentioning in the setup instructions. No big deal, just upload the universe file again and run the dbupdater script again.

[Dutch]

So this is the current RNFL process.

1. Export data from FOF7 to my local machine
2. Using FTP, upload files to the remote server
3. RDP into remote server
4. Run SQL Load Data scripts

That takes about 3 minutes so that's no big deal, but the limiting factor is that our commish (PackerFanatic) has trouble doing so from his work (and I can't run it until I get home from work either).

So I'm hoping I can borrow the code you've written and tweak it for our use. I'm not sure how much tweaking that would take, honestly, but if your PHP page is easily portable to other SQL structures, this would be soooo awesome for any sort of FOF MP league.

One of the reasons we had problems early on was that I couldn't get permissions to run a SQL LOAD DATA script to our host...I'm thinking this could possibly solve that issue. (I have no permission problems now with my current host since I switched us to a VPS).

[aston217]

Yup. That's the table. I will add that to the zip file later this morning. On the other issue, yeah, it isn't going to handle quotation marks within a nickname field. I would just change the nickname.

Should be able to escape that...See: PHP: mysql_real_escape_string - Manual (http://us2.php.net/mysql_real_escape_string)

(Although it's deprecated, but that probably involves the least changing of code)

[3ric]

Should be able to escape that...See: PHP: mysql_real_escape_string - Manual (http://us2.php.net/mysql_real_escape_string)

(Although it's deprecated, but that probably involves the least changing of code)

Single quotes are probably escaped already, it was just the double quotes that caused problems.

[Ben E Lou]

Version 2.02 released.

Added sql statement to create new db table and php code to handle the "team_yearly_information.csv" export added in FOF 7.0b.
Minor bug fixes.Download link in first post updated.

[Ben E Lou]

Very minor update to 2.02 released. I just kept it under the 2.02 name even:

Slight change in code and in translation_season_weeks table to handle Front Office Bowl.

I don't know that anyone is using this actively yet other than me, but if so, you'll want to just DROP TABLE fof7translate_season_weeks and then re-run the section of the dbcreate script that creates that table. It needed one extra line to handle Front Office Bowl vs. End Of Season.

[ZappBrannigan]

I just tried to set up a new league site today with v2.0.2 and I received the

1394 rows inserted into fof7_awards in 0 seconds.
185 rows inserted into fof7_active_quarterbacks in 0 seconds.
121 rows inserted into fof7_active_free_agency in 0 seconds.
67035 rows inserted into fof7_career_records in 18 seconds.
0 rows inserted into fof7_game_information in 0 seconds.
116145 rows inserted into fof7_game_records in 27 seconds.
Table 'cpfl.fof_mappings' doesn't exist
SELECT id FROM fof_mappings m WHERE m.position='QB'

error. I don't see the new table getting set up in the dbcreate.sql file and I don't see anything that looks like it via mysql> show tables;

Was maybe the code accidentally removed from v2.0.2 or am I missing something (more likely)?

thanks,
Mark

[Ben E Lou]

I think I removed it in order to add the Long Snapper bits to it, then forgot to re-add. Here it is. I'll add it back to the zip later this evening.

CREATE TABLE IF NOT EXISTS `fof_mappings` (
`ID` smallint(5) unsigned NOT NULL DEFAULT '0',
`Position` char(4) NOT NULL DEFAULT '',
`PositionGroup` char(3) NOT NULL DEFAULT '',
`TransactionType` char(64) NOT NULL DEFAULT '',
`Precipitation` char(10) NOT NULL DEFAULT '',
`PlayerStatus` char(20) NOT NULL DEFAULT '',
`StaffRole` char(20) NOT NULL DEFAULT '',
`Playoffs` char(20) NOT NULL DEFAULT '',
`Ability` char(12) NOT NULL DEFAULT '',
`StadiumType` char(25) NOT NULL DEFAULT '',
`ConstructionType` char(15) NOT NULL DEFAULT '',
`DriveResult` char(15) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;

--
-- Dumping data for table `fof_mappings`
--

INSERT INTO `fof_mappings` VALUES(0, 'NA', 'NA', 'None', 'Fair', 'Active', 'Head Coach', 'None', 'Poor', 'Outdoor/Grass', 'None', 'None');
INSERT INTO `fof_mappings` VALUES(1, 'QB', 'QB', 'Signed as free agent', 'Rain', 'Injured Reserve', 'Off Coord', 'Wildcard Round', 'Poor', 'Outdoor/Turf', 'Renovation', 'Punt');
INSERT INTO `fof_mappings` VALUES(2, 'RB', 'RB', 'Resigned as an unrestricted free agent', 'Stormy', 'Inactive', 'Def Coord', 'Divisional Round', 'Poor', 'Dome/Turf', 'New Stadium', 'Downs');
INSERT INTO `fof_mappings` VALUES(3, 'FB', 'FB', 'Released', 'Snow', 'Suspended', 'None', 'Conference Round', 'Poor', 'Retractable Roof/Grass', '', 'Half');
INSERT INTO `fof_mappings` VALUES(4, 'TE', 'TE', 'Received player in trade from', '', '', '', 'Conference Champion', 'Poor', '', '', 'Game');
INSERT INTO `fof_mappings` VALUES(5, 'FL', 'WR', 'Received draft pick in trade from', '', '', '', 'League Champion', 'Poor', '', '', 'Fumble');
INSERT INTO `fof_mappings` VALUES(6, 'SE', 'C', 'Retired', '', '', '', '', 'Poor', '', '', 'Interception');
INSERT INTO `fof_mappings` VALUES(7, 'LT', 'G', 'Designated franchise player', '', '', '', '', 'Poor', '', '', 'Safety');
INSERT INTO `fof_mappings` VALUES(8, 'LG', 'T', 'Signed to a new contract', '', '', '', '', 'Poor', '', '', 'FieldGoal');
INSERT INTO `fof_mappings` VALUES(9, 'C', 'P', 'Placed on injured reserve', '', '', '', '', 'Poor', '', '', 'Touchdown');
INSERT INTO `fof_mappings` VALUES(10, 'RG', 'K', 'Suspended for off-field conduct', '', '', '', '', 'Poor', '', '', 'MissedFG');
INSERT INTO `fof_mappings` VALUES(11, 'RT', 'DE', 'Hired as head coach', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(12, 'P', 'DT', 'Hired as lead scout', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(13, 'K', 'ILB', 'Signed to franchise player contract', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(14, 'LDE', 'OLB', 'Franchise player released under cap rule', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(15, 'LDT', 'CB', 'Signed as an unrestricted free agent from', '', '', '', '', 'Poor', '', '', '');
INSERT INTO `fof_mappings` VALUES(16, 'NT', 'S', 'Signed a renegotiated contract', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(17, 'RDT', 'LS', 'Lost draft pick for cap violation', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(18, 'RDE', '', 'Hired as offensive coordinator', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(19, 'SLB', '', 'Hired as defensive coordinator', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(20, 'SILB', '', 'Has begun a contract holdout', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(21, 'MLB', '', 'Has ended his contract holdout', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(22, 'WILB', '', 'Will re-enter the draft', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(23, 'WLB', '', 'Turned down a contract offer', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(24, 'LCB', '', 'Offer withdrawn due to lack of cap room', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(25, 'RCB', '', 'Signed his rookie contract', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(26, 'SS', '', 'Changed primary position to', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(27, 'FS', '', 'Sent to play in summer league', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(28, 'LS', '', 'Injury', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(29, '', '', 'Renovation Vote', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(30, '', '', 'Construction Vote', '', '', '', '', 'Fair', '', '', '');
INSERT INTO `fof_mappings` VALUES(31, '', '', 'Franchise Move', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(32, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(33, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(34, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(35, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(36, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(37, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(38, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(39, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(40, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(41, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(42, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(43, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(44, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(45, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(46, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(47, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(48, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(49, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(50, '', '', '', '', '', '', '', 'Average', '', '', '');
INSERT INTO `fof_mappings` VALUES(51, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(52, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(53, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(54, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(55, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(56, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(57, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(58, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(59, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(60, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(61, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(62, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(63, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(64, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(65, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(66, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(67, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(68, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(69, '', '', '', '', '', '', '', 'Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(70, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(71, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(72, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(73, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(74, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(75, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(76, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(77, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(78, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(79, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(80, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(81, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(82, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(83, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(84, '', '', '', '', '', '', '', 'Very Good', '', '', '');
INSERT INTO `fof_mappings` VALUES(85, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(86, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(87, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(88, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(89, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(90, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(91, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(92, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(93, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(94, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(95, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(96, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(97, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(98, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(99, '', '', '', '', '', '', '', 'Excellent', '', '', '');
INSERT INTO `fof_mappings` VALUES(100, '', '', '', '', '', '', '', 'Excellent', '', '', '');

[ZappBrannigan]

awesome, I think fof_positions got left out too.

thanks for the great utility,
Mark

[Ben E Lou]

Heh. I had two copies of dbcreate.sql sitting on my HD. I must have added the new/changed tables to the old one.

[Ben E Lou]

FWIW, I'm setting up the GML FOF7 database pretty much from scratch this morning, so that should be a good test to make sure the dbcreate and everything else are in sync. (I checked last night and I actually had *four* local copies of dbcreate.sql and three of dbupdater.php. I've pared down to one of each now.) As a data point for those curious about run time for doing player_season, it did 8 seasons in 129 seconds on my server, so roughly 15-20 seconds per season.

[Ben E Lou]

...and then writing everything else was about a minute. (I did player_season_all.csv by itself, then everything else, including transactions_all.csv, together.)

466 rows inserted into fof7_awards in 3 seconds.
191 rows inserted into fof7_active_quarterbacks in 0 seconds.
83 rows inserted into fof7_active_free_agency in 0 seconds.
43343 rows inserted into fof7_career_records in 9 seconds.
0 rows inserted into fof7_game_information in 0 seconds.
112972 rows inserted into fof7_game_records in 21 seconds.
5330 rows inserted into fof7_player_information in 2 seconds.
2810 rows inserted into fof7_player_record in 1 seconds.
0 rows inserted into fof7_standings in 0 seconds.
24457 rows inserted into fof7_playoff_game_records in 3 seconds.
842 rows inserted into fof7_rookies in 1 seconds.
67133 rows inserted into fof7_season_records in 12 seconds.
271 rows inserted into fof7_staff in 1 seconds.
0 rows inserted into fof7_staff_history in 0 seconds.
33001 rows inserted into fof7_team_game_records in 5 seconds.
33 rows inserted into fof7_team_information in 0 seconds.
2760 rows inserted into fof7_team_playoff_game_records in 1 seconds.
3561 rows inserted into fof7_team_season_records in 0 seconds.
0 rows inserted into fof7_team_statistics in 1 seconds.
257 rows inserted into fof7_team_yearly_information in 0 seconds.
993 rows inserted into fof7_team_vs_team in 0 seconds.
19991 rows inserted into fof7_transactions in 3 seconds.


TOTAL SCRIPT RUN TIME: 1:03.

[Ben E Lou]

Hmmm....I'm going to need to allow for game_information_all.csv, standings_all.csv, staff_history_all.csv and team_statistics_all.csv for the initial database population. None of those require consolidating files, but renaming them to the _all to tell dbupdater to load everything rather than just the current season. Adding that now...

[Ben E Lou]

Hmmm....I'm going to need to allow for game_information_all.csv, standings_all.csv, staff_history_all.csv and team_statistics_all.csv for the initial database population. None of those require consolidating files, but renaming them to the _all to tell dbupdater to load everything rather than just the current season. Adding that now...Done. I'm going to re-upload once I'm sure that the GML stuff is working correctly, but this has been implemented and will be included in 2.0.3.

2650 rows inserted into fof7_game_information in 5 seconds.
257 rows inserted into fof7_standings in 0 seconds.
836 rows inserted into fof7_staff_history in 0 seconds.
513 rows inserted into fof7_team_statistics in 0 seconds.


TOTAL SCRIPT RUN TIME: 0:05.

[Ben E Lou]

Version 2.03 is up, linked in first post.

Re-added fof_mappings and fof_positions tables to dbcreate.sql.
Adjusted code to allow for game_information_all.csv, standings_all.csv, staff_history_all.csv and team_statistics_all.csv for the initial database population of a league that is past its first season. (All converted leagues will want to use this, of course.) None of those require consolidating files, but simply rename them to *_all.csv to tell dbupdater to load everything in them rather than just the current season.

[RedDoby]

Ben, I just ran this for the first time and for some reason it is only exporting the player's stats for the most current week from player_season_2013. For example, we just completed week 14 and the only stats in the player_season table now are for week 14. Is there something I am missing from the Get URL? Here is what I used http://www.s487856674.onlinehome.us/NFLsim/League/Data/dbupdater.php?year=2013
Everything else exported fine including all of the transactions for 2013.

[Ben E Lou]

Ah, yeah. I mentioned this for "past the first season," but for player_season it would be past the first week: rename it to player_season_all.csv and it will pick up everything. Just upload universe info with it and it will process the one file.

[Ben E Lou]

On that note, it does assume that an update will be made with every game week in the regular season and playoffs, so by default it just writes the most recent week's stats. If that is a problem for anyone, I suppose it wouldn't be terribly difficult to have a check for the appropriate weeks to update, but it might be a while before I get to adding that feature.

[RedDoby]

Great. I just wanted to be sure that is how you intended it to be. You may want to note on the first post to run weekly. Thanks for the awesome utility. I really appreciate it.

[RedDoby]

12300,41)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '12300,41)' at line 1

This just happened this morning during the Front Office Bowl Stage. All other stages have been working fine.

[RedDoby]

Think I may have figured it out. The stageinfo table has the Front Office Bowl stage named as "End of Season", while the universe file stage is written as "Front Office Bowl". Could that be the problem? I would assume the "End of Season" comes after the "Front Office Bowl".

[Ben E Lou]

Yup. I actually discovered and fixed this a while ago, but I'm guessing you have a version before I made that fix. Run this script:

DELETE FROM `fof7stageinfo` WHERE stageid=50;
INSERT INTO `fof7_stageinfo` VALUES(50, 'Front Office Bowl', 26);
INSERT INTO `fof7_stageinfo` VALUES(51, 'End Of Season', 27);

That should take care of it.

[RedDoby]

Got to love an easy fix :). Thanks.

[RedDoby]

I am not able to get the Front office bowl player_season stats to insert into the player_season table. I am at the end of the season stage for 2013.

0 rows inserted into fof7_player_season in 4 seconds.


TOTAL SCRIPT RUN TIME: 0:04.

NEXT STAGE: 2013 End Of Season

Maybe there is something else that I need to fix to get these inserted?

[marc_rdfl]

Sorry, another question.

Why can't I find a csv for the team schedules?

[Ben E Lou]

Because it doesn't exist. In my opinion, that's the biggest thing missing from the FOF7 export process.

[Solecismic]

Because it doesn't exist. In my opinion, that's the biggest thing missing from the FOF7 export process.

This is one more thing I'll add if I get through Greenlight.

[Ben E Lou]

This is one more thing I'll add if I get through Greenlight.:thumbsup:

[aston217]

Awwwwwwwwwwwwwwwwwwwww yeaaaaaaaaaaaaaaaaaaaaa

[Ben E Lou]

Version 2.0.4 uploaded. Link in first post updated. Added fof7_leagueschedule table from the new current_schedule.csv in FOF 7.1.

If you're upgrading, just run this script to add the new table, then replace your dbupdater.php file with the new one in the zip:
CREATE TABLE IF NOT EXISTS `fof7_leagueschedule` (
`year` smallint(4) NOT NULL,
`game_id` smallint(3) NOT NULL,
`week` varchar(19) NOT NULL,
`visitor_team` tinyint(2) NOT NULL,
`home_team` tinyint(2) NOT NULL,
KEY `year` (`year`,`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Note that I'm adding two columns that aren't in the table, 'year' and 'gameid'. The gameid matches the gameid in fof7_game_information so those tables can be joined in a query if necessary.

[Ben E Lou]

i've created a process that automatically picks the files needed for upload for the specific stage that you're in. It requires that you have a local installation of php/MySQL on your laptop or desktop, so I'm not releasing it as an "official" part of DBUpdater, but if anyone using DBUpdater has such a setup, it's a nice little time-saver that I can make available. It also reads the current season and creates the DBUpdater link with the correct URL. I just ran it a few moments ago for the GML's bowl game stage. Here's the output:

Successfully uploaded awards.csv.
Successfully uploaded game_information.csv.
Successfully uploaded player_information.csv.
Successfully uploaded player_record.csv.
Successfully uploaded player_season_2023.csv.
Successfully uploaded playoff_game_records.csv.
Successfully uploaded staff_history.csv.
Successfully uploaded team_information.csv.
Successfully uploaded team_playoff_game_records.csv.
Successfully uploaded team_vs_team.csv.
Successfully uploaded transactions_2023.csv.
Successfully uploaded universe_info.csv.
<a href="http://www.fof-gml.com/dbupdater.php?year=2023">GML 2023 DBUpdater Link</a>

It would require some modification for the correct folders and whatnot, but I'm guessing that for anyone who has gone through the effort to set up a local php/MySQL installation, the effort to modify this for your usage would be trivial. Just let me know.

[marc_rdfl]

I have some questions please:

1) What are the fields prefixed S_ and P_ in the fof7_player_record table?
2) What is the Pointer_to_active_Free_Agent_Block field in fof7_player_record?
3) What s the Pointer_to_player_information_record field in fof7_player_record?
4) Player_season does not appear to hold exhibition stats - correct?

Many thanks

Marc.

[Ben E Lou]

I have some questions please:

1) What are the fields prefixed S_ and P_ in the fof7_player_record table?
2) What is the Pointer_to_active_Free_Agent_Block field in fof7_player_record?
3) What s the Pointer_to_player_information_record field in fof7_player_record?
4) Player_season does not appear to hold exhibition stats - correct?

Many thanks

Marc.1. I believe that the first one holds current-season (S) and current-season playoff (P) stats. Because I need to recalc current-season DawgPoints after each week and my process for doing it used fof_playergamestats in FOF2K7, I use the table that most closely mirrors that one: fof7_player_season. So I don't use any of those fields.

2. I've never bothered to check it. I assume it links to free_agency_record in fof7_active_free_agency, but given that the fof7_active_free_agency table has the player_id field in it, I don't see how it's useful.

3. Again, no idea and never bothered to check. I link fof7_player_record to fof7_player_information with player_id.

4. Correct.

[marc_rdfl]

1. I believe that the first one holds current-season (S) and current-season playoff (P) stats. Because I need to recalc current-season DawgPoints after each week and my process for doing it used fof_playergamestats in FOF2K7, I use the table that most closely mirrors that one: fof7_player_season. So I don't use any of those fields.

2. I've never bothered to check it. I assume it links to free_agency_record in fof7_active_free_agency, but given that the fof7_active_free_agency table has the player_id field in it, I don't see how it's useful.

3. Again, no idea and never bothered to check. I link fof7_player_record to fof7_player_information with player_id.

4. Correct.

Perfect, thanks Ben.

[gRaider2001]

Hello All,

Sorry for bumping an old topic.

Will DBUpdater 2 work for Front Office Football Eight? If so, does anybody have a working link? The link in the original post is no longer working.

[hrd12]

Ben, you mentioned in another thread that you may need to make some changes to the dbcreate.sql script for some of the csvs (SQL and FOF - Front Office Football Central (http://www.operationsports.com/fofc/showthread.php?t=92970))

I tried this from a fresh database and used the info in this thread to update dbcreate.sql to include the missing stuff and it seems to have worked for everything but one record.

60 rows inserted into fof7_awards in 0 seconds.
9 rows inserted into fof7_active_free_agency in 0 seconds.
12112 rows inserted into fof7_career_records in 2 seconds.
64 rows inserted into fof7_game_information in 0 seconds.
53635 rows inserted into fof7_game_records in 11 seconds.
322 rows inserted into fof7_leagueschedule in 6 seconds.
3019 rows inserted into fof7_player_information in 1 seconds.
2886 rows inserted into fof7_player_record in 2 seconds.
0 rows inserted into fof7_standings in 0 seconds.
4392 rows inserted into fof7_playoff_game_records in 0 seconds.
853 rows inserted into fof7_rookies in 1 seconds.
11692 rows inserted into fof7_season_records in 2 seconds.
284 rows inserted into fof7_staff in 0 seconds.
160 rows inserted into fof7_staff_history in 0 seconds.
6118 rows inserted into fof7_team_game_records in 1 seconds.
33 rows inserted into fof7_team_information in 0 seconds.
441 rows inserted into fof7_team_playoff_game_records in 0 seconds.
641 rows inserted into fof7_team_season_records in 0 seconds.
64 rows inserted into fof7_team_statistics in 0 seconds.
993 rows inserted into fof7_team_vs_team in 1 seconds.
INSERT INTO fof7_player_season VALUES("0","1","1","2016","0","1","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","1","1","44","4","5","0","0","0","0","7","16","0","0","0","0","0","0","0","0","0","0","0","1","1","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","8","0","0","0","0","0","0","0","0")
Column count doesn't match value count at row 1

I'm not sure of the issue with this record other than maybe the uniqueid can't be 0? But in any event I believe it mostly worked. I did notice there aren't any rows in a few of the tables but I think that's an issue with the files I combined together to create the 'all' files. I'll look into that though.

[Ben E Lou]

I actually started on a v3.0 update this weekend. There are a few improvements to db structure that I've made over time that I'd like to incorporate.

[hrd12]

Outstanding, thanks Ben. Ill kick in some money to your PayPal account for your efforts!

[Ben E Lou]

Well don't do that until I finish. ;)

Seriously, I should be able to get this out this weekend.

--Ben

[hrd12]

Hey Ben, any luck with the db updater tool?