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.
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, 345 hits)
