SQL and FOF

[Squirrel]

So I taught myself the basics of SQL over the Easter weekend.

I've been through the archive here and can't see much guidance on using SQL to go through the FOF CSVs, other than various commish threads getting into the details of how they put websites together.

Am I missing any obvious resources that will help me learn this faster? So far all I have done is generate 100 years of history, imported it into SQL and I'm playing around with it.

[Ben E Lou]

Originally Posted by Squirrel

So I taught myself the basics of SQL over the Easter weekend.

I've been through the archive here and can't see much guidance on using SQL to go through the FOF CSVs, other than various commish threads getting into the details of how they put websites together.

Am I missing any obvious resources that will help me learn this faster? So far all I have done is generate 100 years of history, imported it into SQL and I'm playing around with it.

In May 1987, my Senior year of high school, I took the AP Computer Science exam (in Pascal). After that day, I never took another computer class. I also never even wrote another single line of code until Fall 2008, when I decided to teach myself PHP and MySQL to examine FOF stats. At the time I had no designs on doing any of the fancier stuff that I do now; I just wanted to calculate a few stats that weren't in the game or on league web sites (like YAC/catch, for example.) I bought PHP/MySQL For Dummies and started doing stuff. Since then, I've taught myself quite a bit and have developed my league sites and the various bells and whistles that you see. From time to time, I've gotten some help from FOFCers, but mostly these days I use W3schools and Stack Overflow (online coding references) when I run into something that I don't know how to do. That said, I'd be happy to give some guidance on specific things.

Point being, a LOT can be done with being self-taught with a nudge here and there to help get you through some sticky points. My #1 bit of advice would be "try to pull information that you want to see." In other words, if it's "fun," it'll give you more of a sense of accomplishment and keep you motivated to learn more.

[Ben E Lou]

Dola:

For clarification, are you using MySQL or SQL? MySQL is the free version that comes with many web hosting plans and can also be installed locally on your machine for free. If you'reusing MySQL, I'd *HIGHLY* recommend the aforementioned "Dummies" book.

[Squirrel]

Thanks Ben.

Yeah I'm using MySQL now - I started with the SQlite add-on within Firefox to learn a little about how queries worked, then once I'd got that into my head I put it all up in a webserver and figured out how to set up phpmyadmin. Will find a copy of the book.

(and FWIW for fellow newbies, I've found a couple of primers from the sabermetrics world useful here: This article which piqued my interest, and then I found this online course which has got me onto the learning curve.)

[cleancoder42]

I'm a software developer, who uses SQL on a daily basis. Feel free to PM me any questions you might have, I'd be happy to help.

[Squirrel]

Cool, thanks. The more I get into this, the more interesting it gets. Starting to think I should have done it for a living!

[redfox000]

Originally Posted by cleancoder42

I'm a software developer, who uses SQL on a daily basis. Feel free to PM me any questions you might have, I'd be happy to help.

Same here.

[hrd12]

Ok SQL guys, I'm trying to create a query which helps me find the guys that are affinities for one of my players. How would I join the players information with the calendar so I don't get duplicates? There's nothing to join on like a player id so I'm a little confused. For example I've got a player born on 1/26 (I catted the month and day fields from the player information table get the bday field. The other 3 fields are from a calendar table I created)
+-------+-------+-------+-------------+
| bday | begin | end | sign |
+-------+-------+-------+-------------+
| 01/26 | 01/21 | 02/18 | Aquarius |
| 01/26 | 02/19 | 03/20 | Pisces |


There's 12 lines that show his bday and the other signs. He falls into the Aquarius category obviously, so how would I only display that?

[cleancoder42]

So first I'd change your calendar table to look like this:
CalendarID BeginMonth BeginDay EndMonth EndDay StarSign

Then you can query like this:

SELECT
info.First_Name,
info.Last_Name,
info.Month_Born,
info.Day_Born,
info.Year_Born,
c.SignName,
c.*
FROM
dbo.player_information info
INNER JOIN dbo.Calendar c ON (info.Month_Born = c.BeginMonth AND info.Day_Born> c.BeginDay) OR (info.Month_Born = c.EndMonth AND info.Day_Born < c.EndDay)
WHERE
info.Player_ID = {the player_id of the player in question}

That get's you the sign of any player you want. If you want to get all players with affinities to that sign, it will be a different query, and I'll post that after I get back from my daughters soccer practice.

[cleancoder42]

So if you want to get all the affinities for a player you can do this.
Say your player is Aquarius, so you want all the players that are Libra or Capricorn, you could do this
SELECT
info.First_Name,
info.Last_Name,
info.Month_Born,
info.Day_Born,
info.Year_Born,
info.Position,
c.SignName
FROM
dbo.player_information info
INNER JOIN dbo.Calendar c ON (info.Month_Born = c.BeginMonth AND info.Day_Born> c.BeginDay) OR (info.Month_Born = c.EndMonth AND info.Day_Born < c.EndDay)
WHERE
(c.SignName = 'Libra' OR c.SignName = 'Capricorn')

[cleancoder42]

And if you want just one position that matches affinity you could do this:
SELECT
info.First_Name,
info.Last_Name,
info.Month_Born,
info.Day_Born,
info.Year_Born,
info.Position,
c.SignName
FROM
dbo.player_information info
INNER JOIN dbo.Calendar c ON (info.Month_Born = c.BeginMonth AND info.Day_Born> c.BeginDay) OR (info.Month_Born = c.EndMonth AND info.Day_Born < c.EndDay)
WHERE
(c.SignName = 'Libra' OR c.SignName = 'Capricorn') AND info.Position = 'RB'

Which gets all Running backs that are an affinity

[cleancoder42]

And honestly I would set up my calendar table like this:
CalendarID, BeginMonth, BeginDay, EndMonth, EndDay, StarSign, ConflictID, Affnity1ID, Affinity2ID

Where ConflictID, Affiity1ID, and Affinity2ID are the ID's of the signs that correspond with that sign. So the table row for Aquarius would look like this:

1, 1, 21, 2, 18, 'Aquarius', 2, 9, 12

where the ID of 9 corresponds to Libra, 12 corresponds to Capricorn, and 2 corresponds to Pisces.

[Squirrel]

Woah. Bookmarking this. Right now I do it all in Excel and it's way more convoluted than this. Thanks.

A simpler question if I may. What's the best way to get all the csv tables into MySQL? Ideally I want to be able to click on 'export data' and 'export scouting data' in-game to refresh the CSVs, then somehow automatically update all that into SQL with one click / minimum hassle. Is there a way to do that?

[cleancoder42]

I use MSSQL instead of MYSQL, and I have my own scripts that do the import.

There is this thread: DBUpdater 2 For FOF7 - Front Office Football Central

That includes a tool for importing the csv's into MYSQL. It says it's for FOF7, but the csv tables haven't changed much between versions. I think it might be the one Ben uses for his online leagues.

[hrd12]

Cleancoder this is great stuff. Thanks so much! I never thought of setting up the calendar table like that. ☺️

[Ben E Lou]

Originally Posted by cleancoder42

I use MSSQL instead of MYSQL, and I have my own scripts that do the import.

There is this thread: DBUpdater 2 For FOF7 - Front Office Football Central

That includes a tool for importing the csv's into MYSQL. It says it's for FOF7, but the csv tables haven't changed much between versions. I think it might be the one Ben uses for his online leagues.

They're very similar, but a few (staff.csv and player_record.csv come to mind immediately) have changed. I have a version that I'm using for FOF8. The issue with updating the release to 3.0 is making a new dbcreate.sql to do the initial setup. Only a few people used 2.0, so it's hard getting motivated to do that, but if Squirrel or others request it, I can get it going.

[hrd12]

I'd definitely be interested in it.