NFL Pick ‘Em for 2019 Season

NFL PHP Pick 'Em is a free php web app that allows you to host a weekly NFL pick 'em football pool on your website.  This fantastic tool replaced the use of spreadsheets.  There was a time that this tool was not supported.  However, I just checked out the www.phpickem.org website it is is updated for 2019. I wish I knew that before I did the following.

I was asked to update an existing installation of PHP Pick 'Em that had been used for a few years and has had some customization applied to it.  The admin didn't want to change anything but the schedule.

To build a new schedule, is not fun and I wish I knew of a better way, but here is how I did it.

Go to the hosted site http://example.com/buildSchedule.php. This will build an xls file of the schedule.  This is a screenshot of a sampling of that output.

We are going to want the end result to look like this.

Basically, I added a column and did a A1+1 A2+1 etc for the first column of the spreadsheet.  Added three more columns with proper headers and order to match the existing SQL export.

  • gameID (adding each previous cell A1+1 A2+1 until 256.)
  • homeScore NULL
  • vistorScore NULL
  • overtime 0

All this effort completed in a spreadsheet looks something like this.

Note that the dates and times are problematic.

# from
9/8/2019 22:00
# to
2019-09-08 22:00:0
  • Copy the time and date column ("gametimeEastern") to a text file on Linux and manipulated that one column like so.  Assume that filename is time.txt.
# pad all dates with zeros, run twice to catch stragglers
sed -i 's/\<[0-9]\>/0&/' time.txt
sed -i 's/\<[0-9]\>/0&/' time.txt
# Append a:00 to end of each line
sed -i 's/$/:00/' time.txt
# change dateformat from MM/DD/YYYY to YYYY-MM-DD
sed -i -E 's,([0-9]{2})/([0-9]{2})/([0-9]{4}),--,g' time.txt
  • Copy this final output of time.txt into the column to replace in the spreadsheet.
  • Save the spreadsheet as a CSV.

  • Copy the CSV content to a linux box again.
  • Run the following commands against it to complete the conversion.
# add ), to end of line
sed -i 's/$/),/' nfl.csv
# add ( to beginning of line
sed -i 's/^/(/' nfl.csv

  • Add single quotes around fields 3, 4, and 6.
# these commands add single quotes to specific fields 3, 4, and 6. 
awk $'BEGIN { FS = OFS = "," } { $3= "\'" $3 "\'"; print }' nfl.csv > dd 
awk $'BEGIN { FS = OFS = "," } { $4= "\'" $4 "\'"; print }' dd > ee 
awk $'BEGIN { FS = OFS = "," } { $6= "\'" $6 "\'"; print }' ee > nfl2.csv

  • Copy the 256 lines into the SQL and import it.
  • Change the final line from a comma (,) to a semi-colon (;)
  • Import into the database.
  • Done.

Download

The final result of the effort offered with no warranty or liability.  This SQL command has a DROP TABLE IF EXISTS.

  nflp_schedule-2019.sql (16.7 KiB, 21 hits)

Next steps

  • Empty last years pics (two tables).
    • nflp_picksummary
    • nflp_pics

 

Source(s)

  • https://stackoverflow.com/questions/38460805/wrap-a-single-field-with-single-quotes-using-awk
  • https://stackoverflow.com/questions/16490698/how-to-add-single-quotes-around-columns-using-awk
  • https://stackoverflow.com/questions/4080526/using-sed-to-insert-text-at-the-beginning-of-each-line
  • https://unix.stackexchange.com/questions/9137/zero-fill-numbers-to-2-digits-with-sed
  • https://stackoverflow.com/questions/15978504/add-text-at-the-end-of-each-line
  • https://stackoverflow.com/questions/18453057/use-sed-or-awk-to-fix-date-format