Front Office Football Central - Alternate Report Generation

Front Office Football Central (https://forums.operationsports.com/fofc//index.php)

- FOF9, FOF8, and TCY Discussion (https://forums.operationsports.com/fofc//forumdisplay.php?f=33)

- - Alternate Report Generation (https://forums.operationsports.com/fofc//showthread.php?t=69283)

Alternate Report Generation

I've been curious as to how some of the better looking web pages for teams, schedules, rosters, etc. are being generated. The vNFL and FOWL leagues in particular have very nice looking web sites and reports.

Is the utility suite created by gstelmack being used to do this? Is it something else? Is it purely custom?

They're very nice looking and I'm jealous of them.:)

Most leagues with custom reports use at least some of the Stelmack tools (mainly Interrogator and DBUpdater) to help create and maintain a database. Php is the most popular language used to code pages to generate the reports from the databases. There is some cross-pollenation and exchange of ideas, but for the most part, the php code is pretty much custom from league to league (with the exception of cases where the same person is maintaining the code and database for multiple leagues).

Php isn't difficult to learn at all with a bit of time and a willingness to dig in and apply yourself. I hadn't coded anything since high school (over 20 years ago), but I picked up a couple of books on PHP, SQL and HTML less than 2 months ago, and have the FOWL and WOOF sites to show for it in that time.

Thanks a million for the information. I've been meaning to learn some PHP and mySQL programming anyway.

I've successfully set up PHP and MySQL and have populated a database using DBUpdater. Thanks, Ben, for the information on this.

The next hurdle of coure is making use of all of this. One of the first things I tried to do was to generate a basic page containing the standings. However, I don't see any useful tables in the database for doing this.

fof_franchise has just the right columns, but doesn't appear to be updated until the end of the season.

fof_teamschedule can derive the information, but the query is pretty large and messy.

Any tips?

Quote:

Originally Posted by chad56s (Post 1903856)

Your best bet is to do it from fof_teamschedule for the current season. Here's a basic one, run on my IHOF database. (The league is between weeks 4 and 5 of 2019 right now.)

Code:

SELECT

  cityname,

  sum(score>oppscore) AS wins,

  sum(oppscore>score) AS losses,

  sum(score=oppscore) AS ties,

FROM fof_teamschedule s

JOIN fof_teams t ON t.id = s.teamid

WHERE year=2019 AND week>5 AND week < 23 AND (score>0 OR oppscore>0)

GROUP BY teamid

ORDER BY conference, division, wins DESC, cityname

This returns...

cityname	wins	losses
Syracuse	4	0
Boston	3	1
Rochester	2	2
North Plainfield	1	3
Houston	3	1
Fort Worth	2	2
Vicksburg	1	2
San Antonio	1	3
Georgia	3	1
Orlando	2	2
Tucker	2	2
Conyers	1	3
Gothenburg	3	1
Maassluis	2	2
Bordeaux	1	2
Paris	1	3
Minnesota	3	1
Muscatine	2	2
Michigan	1	2
Chicago	1	3
Asheville	4	0
Miami	2	2
Outer Banks	2	2
Knoxville	1	3
Frederick	3	1
Williamsburg	3	1
Chesapeake	2	2
Brooklyn	1	2
Albuquerque	2	2
Oakland	2	2
Boulder	1	3
Fairbanks	0	4

Note that I'm excluding games in which both teams don't score. It's the easiest way I could think of to keep all the unplayed games in a season from showing up as ties. You *could* pull the current stage from fof_gameinfo and compare it. That's more clean, but it's also more work. As long as there are no scoreless ties (and I've never seen one in any of my SP or MP leagues), this way of doing it works just fine. ;)

With a bit of formatting and the addition of some more stuff to the query, you can eventually get the following data all from that table:

STANDINGS

I had been using non-zero attendance to determine whether a game had been played, until the AUFL had some teams who have had 0 attendance this season. Now I exclude games with 0 attendance and 0 scores.

Quote:

Originally Posted by jdavidbakr (Post 1904300)

...until the AUFL had some teams who have had 0 attendance this season...

:lol:

Wow, that's great! I didn't realize you could do a sum on a comparison like that. That simplifies things immensely.

Thanks for the help!

I didn't either until I just tried it out. ;)

Seriously, so far every time I've had the thought "I wonder if it will let me...," it has let me. There have been times that I've had to go to the interwebs or to FOFCers for help with syntax, but in terms of flexibility, this stuff is the bizomb to me.