Besides crunching more code, primarily working on the edit inventory form for the site, this week Professor Bob Ellison and Greg Miller helped me get set up on one of UPB’s web servers so I could get the site going live in hopes of getting more people incorporated into testing. Overall, the process went pretty well with the exception of one slightly painful hiccup. Thankfully, MySQL Workbench makes exporting the database easy. Here is a peek of the process. My database is simply called “SA”.
In the interest of “least privilege”, I really have no business logging into the actual web server. Professor Ellison set me up with a VPN role that initializes a connection to the web server and gave me access to the share that the website files are running from. Using Pulse Secure, a nice program to connect to the VPN, I simply connect to the server and then map a drive to that share.
At this point, I am able to make changes to the .php, .css, .html, and .js files on my local machine, map a drive to the share on the webserver, copy the files over, and then view them online. The only downside to this is that in my local environment, for testing purposes, I often find myself destroying and recreating the database by running my SQL script. When testing, I repeatedly enter useless information into the database. When I make a big change, or find there is a database table that I decide to change, I simply tweak and run the database script and recreate the database. The database is not being housed on the same web server that my share is set up to, so I am unable to copy a newer version of the database over myself.
My local machine is simply a Windows 10 laptop. I’m running IIS and MySQL locally. On the web server where the production site is actual being hosted, IIS is also being used, so no big surprises there. However, the database is being hosted on a Linux server. After changing the connection settings from the old database location to the new database location, I excitedly hit the refresh button but only to be disappointed when my built-in database queries failed on loading the page. After a little troubleshooting, I realized what the problem was.
I have a very specific way that I’ve handled the MySQL queries in my PHP pages and my .SQL script. In my database script, I had named the tables “Inventory”, “Person”, “Building”, etc. When I query those tables, I use the same exact names; for example, “SELECT Item_ID FROM Inventory”, which has always worked perfectly. Here’s the caveat; Linux is case sensitive and does not allow table names to have capital letters in them. When I exported the database, every table was recreated with all lowercase letters (from “Inventory” to “inventory”). All of my SQL queries are set up to query from “Inventory”, not “inventory” – so ultimately every query (at least 20) scattered throughout my site needed to be tweaked to not include capital letters in the table name. Overall, it was a minor setback and almost kind of funny, but it still took time to go through every PHP file and tweak a couple of letters here and there. It’s never fun fixing old code when you could be writing new code. Needless to say, I will never use capital letters when querying a MySQL table.