DBUpdater 2 For FOF7

Ben E Lou · 03-05-2014, 12:58 PM

LINK: http://www.younglifenorthdekalb.com/...aterv2.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:
1. 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.)
2. 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.)
3. 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
4. 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 · 03-05-2014, 07:08 PM

Awesome stuff. Good job, Ben!

WheelsVT · 03-05-2014, 08:42 PM

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

PackerFanatic · 03-05-2014, 11:07 PM

Very, very nice. Good work Ben!

3ric · 03-07-2014, 07:11 AM

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 · 03-07-2014, 07:14 AM

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

Code:

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 · 03-07-2014, 07:17 AM

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 · 03-07-2014, 07:28 AM

We'll make a programmer out of you yet!

3ric · 03-07-2014, 07:35 AM

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 · 03-07-2014, 07:40 AM

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

Code:

-- 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 · 03-07-2014, 07:41 AM

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

Ben E Lou · 03-07-2014, 07:44 AM

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

3ric · 03-07-2014, 07:47 AM

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

Code:

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 · 03-07-2014, 08:04 AM

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 · 03-07-2014, 08:11 AM

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 · 03-07-2014, 08:36 AM

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 · 03-07-2014, 08:40 AM

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 · 03-07-2014, 08:43 AM

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 · 03-07-2014, 08:44 AM

Quote:

Originally Posted by 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.

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

3ric · 03-07-2014, 08:46 AM

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 · 03-07-2014, 08:50 AM

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 · 03-07-2014, 10:14 AM

Quote:

Originally Posted by 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.

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 · 03-07-2014, 10:18 AM

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 · 03-07-2014, 01:36 PM

Quote:

Originally Posted by Ben E Lou

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 · 03-07-2014, 07:14 PM

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 · 03-08-2014, 03:46 AM

Quote:

Originally Posted by 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.

Should be able to escape that...See: PHP: mysql_real_escape_string - Manual

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

3ric · 03-09-2014, 11:28 AM

Quote:

Originally Posted by aston217

Should be able to escape that...See: PHP: mysql_real_escape_string - Manual

(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 · 03-16-2014, 09:33 AM

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 · 03-29-2014, 11:04 AM

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 · 03-30-2014, 07:22 PM

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

Quote:

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 · 03-30-2014, 07:52 PM

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.

Code:

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 · 03-30-2014, 08:07 PM

awesome, I think fof_positions got left out too.

thanks for the great utility,
Mark

Ben E Lou · 03-30-2014, 08:18 PM

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 · 03-31-2014, 06:04 AM

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 · 03-31-2014, 06:08 AM

...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.)

Quote:

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 · 03-31-2014, 06:10 AM

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 · 03-31-2014, 06:26 AM

Quote:

Originally Posted by 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.

Quote:

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 · 03-31-2014, 07:29 AM

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 · 05-08-2014, 02:47 PM

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/....php?year=2013
Everything else exported fine including all of the transactions for 2013.

Ben E Lou · 05-08-2014, 03:03 PM

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 · 05-08-2014, 03:22 PM

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 · 05-08-2014, 07:24 PM

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 · 06-15-2014, 10:18 AM

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 · 06-15-2014, 10:30 AM

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 · 06-15-2014, 11:17 AM

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 · 06-15-2014, 11:35 PM

Got to love an easy fix

. Thanks.

RedDoby · 06-22-2014, 03:40 PM

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 · 10-31-2014, 03:59 AM

Sorry, another question.

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

Ben E Lou · 10-31-2014, 04:16 AM

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

Solecismic · 10-31-2014, 04:39 AM

Quote:

Originally Posted by Ben E Lou

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.

03-05-2014, 11:07 PM	#4
PackerFanatic Pro Starter Join Date: Jul 2005 Location: Appleton, WI	Very, very nice. Good work Ben! __________________ Commissioner of the RNFL

03-07-2014, 07:11 AM	#5
3ric College Starter Join Date: Dec 2000 Location: Sweden	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. __________________ San Diego Chargers (HFL) - Lappland Reindeers (WOOF) - Gothenburg Giants (IHOF) Indiana: A TCY VC - year 2044 - the longest running dynasty ever on FOFC! Last edited by 3ric : 03-07-2014 at 07:13 AM.

03-07-2014, 07:17 AM	#7
3ric College Starter Join Date: Dec 2000 Location: Sweden	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. __________________ San Diego Chargers (HFL) - Lappland Reindeers (WOOF) - Gothenburg Giants (IHOF) Indiana: A TCY VC - year 2044 - the longest running dynasty ever on FOFC! Last edited by 3ric : 03-07-2014 at 07:21 AM.

03-07-2014, 07:28 AM	#8
gstelmack Pro Starter Join Date: Oct 2000 Location: Cary, NC	We'll make a programmer out of you yet! __________________ -- Greg -- Author of various FOF utilities

03-07-2014, 07:35 AM	#9
3ric College Starter Join Date: Dec 2000 Location: Sweden	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' __________________ San Diego Chargers (HFL) - Lappland Reindeers (WOOF) - Gothenburg Giants (IHOF) Indiana: A TCY VC - year 2044 - the longest running dynasty ever on FOFC!

03-05-2014, 07:08 PM	#2
Dutch "Dutch" Join Date: Oct 2000 Location: Tampa, FL	Awesome stuff. Good job, Ben!

03-05-2014, 08:42 PM	#3
WheelsVT High School JV Join Date: Sep 2002 Location: GA	Awesome stuff Ben. Enabling the next generation of great league sites.

03-07-2014, 07:41 AM	#11
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	(Oh, and that table has been modified to include LS.) __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-07-2014, 07:44 AM	#12
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	2.0.1 released with scripts to create those two tables. Link in first post updated. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-07-2014, 08:04 AM	#14
3ric College Starter Join Date: Dec 2000 Location: Sweden	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") __________________ San Diego Chargers (HFL) - Lappland Reindeers (WOOF) - Gothenburg Giants (IHOF) Indiana: A TCY VC - year 2044 - the longest running dynasty ever on FOFC!

03-07-2014, 08:11 AM	#15
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-07-2014, 08:36 AM	#16
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-07-2014, 08:40 AM	#17
3ric College Starter Join Date: Dec 2000 Location: Sweden	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 __________________ San Diego Chargers (HFL) - Lappland Reindeers (WOOF) - Gothenburg Giants (IHOF) Indiana: A TCY VC - year 2044 - the longest running dynasty ever on FOFC!

03-07-2014, 08:43 AM	#18
3ric College Starter Join Date: Dec 2000 Location: Sweden	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. __________________ San Diego Chargers (HFL) - Lappland Reindeers (WOOF) - Gothenburg Giants (IHOF) Indiana: A TCY VC - year 2044 - the longest running dynasty ever on FOFC!

03-07-2014, 08:46 AM	#20
3ric College Starter Join Date: Dec 2000 Location: Sweden	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. __________________ San Diego Chargers (HFL) - Lappland Reindeers (WOOF) - Gothenburg Giants (IHOF) Indiana: A TCY VC - year 2044 - the longest running dynasty ever on FOFC!

03-07-2014, 08:50 AM	#21
3ric College Starter Join Date: Dec 2000 Location: Sweden	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 __________________ San Diego Chargers (HFL) - Lappland Reindeers (WOOF) - Gothenburg Giants (IHOF) Indiana: A TCY VC - year 2044 - the longest running dynasty ever on FOFC!

03-07-2014, 10:18 AM	#23
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-07-2014, 07:14 PM	#25
Dutch "Dutch" Join Date: Oct 2000 Location: Tampa, FL	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). Last edited by Dutch : 03-07-2014 at 07:15 PM.

03-16-2014, 09:33 AM	#28
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	Version 2.02 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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-29-2014, 11:04 AM	#29
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-30-2014, 08:07 PM	#32
ZappBrannigan n00b Join Date: Nov 2009	awesome, I think fof_positions got left out too. thanks for the great utility, Mark

03-30-2014, 08:18 PM	#33
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	Heh. I had two copies of dbcreate.sql sitting on my HD. I must have added the new/changed tables to the old one. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-31-2014, 06:04 AM	#34
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-31-2014, 06:10 AM	#36
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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... __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

03-31-2014, 07:29 AM	#38
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	Version 2.03 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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!*

05-08-2014, 02:47 PM	#39
RedDoby n00b Join Date: Nov 2010	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/....php?year=2013 Everything else exported fine including all of the transactions for 2013.

05-08-2014, 03:03 PM	#40
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

05-08-2014, 03:22 PM	#41
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'! Last edited by Ben E Lou : 05-08-2014 at 03:23 PM.

05-08-2014, 07:24 PM	#42
RedDoby n00b Join Date: Nov 2010	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.

06-15-2014, 10:18 AM	#43
RedDoby n00b Join Date: Nov 2010	Error during Front Office Bowl Stage 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.

06-15-2014, 10:30 AM	#44
RedDoby n00b Join Date: Nov 2010	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". Last edited by RedDoby : 06-15-2014 at 10:33 AM.

06-15-2014, 11:17 AM	#45
Ben E Lou Morgado's Favorite Forum Fascist Join Date: Oct 2000 Location: Greensboro, NC	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. __________________ The media don't understand the kinds of problems and pressures 54 million come wit'!

06-15-2014, 11:35 PM	#46
RedDoby n00b Join Date: Nov 2010	Got to love an easy fix . Thanks.

06-22-2014, 03:40 PM	#47
RedDoby n00b Join Date: Nov 2010	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?

10-31-2014, 03:59 AM	#48
marc_rdfl n00b Join Date: Jul 2009	Sorry, another question. Why can't I find a csv for the team schedules?