NFL Pick ‘Em for 2019 Season

Contents

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 



    

  • Copy the 256 lines into the SQL and import it.
    • 

  • Import into the database.
    • 

  • Done.
    • 



Bonus


This command was used for a different season, but will be documented here for prosperity.


#remove the third comma from each row and replace with a space
[root@web nfl]# sed 's/,/ /3' nfl2.csv
268,18,2021-01-09 13:00:00,TB,NULL,CAR,NULL,0


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, 492 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
  • https://unix.stackexchange.com/questions/162377/sed-remove-the-very-last-occurrence-of-a-string-a-comma-in-a-file

BEGIN { FS = OFS = “,” } { $3= “\'” $3 “\'”; print }’ nfl.csv > dd awk

  • Copy the 256 lines into the SQL and import it.
  • Import into the database.
  • Done.

Bonus

This command was used for a different season, but will be documented here for prosperity.



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, 492 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
    • 

  • https://unix.stackexchange.com/questions/162377/sed-remove-the-very-last-occurrence-of-a-string-a-comma-in-a-file
    • 




BEGIN { FS = OFS = “,” } { $4= “\'” $4 “\'”; print }’ dd > ee 
awk 







    

  • Copy the 256 lines into the SQL and import it.
    • 

  • Import into the database.
    • 

  • Done.
    • 



Bonus


This command was used for a different season, but will be documented here for prosperity.




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, 492 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
    • 

  • https://unix.stackexchange.com/questions/162377/sed-remove-the-very-last-occurrence-of-a-string-a-comma-in-a-file
    • 




BEGIN { FS = OFS = “,” } { $6= “\'” $6 “\'”; print }’ ee > nfl2.csv
# Change the final line, last comma (,) to a semi-colon (;)
sed -i ‘$s/,$/;/’ nfl2.csv







    

  • Copy the 256 lines into the SQL and import it.
    • 

  • Import into the database.
    • 

  • Done.
    • 



Bonus


This command was used for a different season, but will be documented here for prosperity.




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, 492 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
    • 

  • https://unix.stackexchange.com/questions/162377/sed-remove-the-very-last-occurrence-of-a-string-a-comma-in-a-file
    •