Oh geez, that was laboured…
Anyway. Who knew building a mere web page to make a quick SQL query to a database would would be something I thought warranted me waffling about it?
So here’s the deal: over the last few weeks I worked on a small side project at work to build a web page that would display the results of a SQL query/lookup.
This was new for me, since I’m far more used to maintaining the servers that host these things, not building the things themselves. Still, building this has been, dare I say, a rather fun time. After all the troubleshooting (though that holds its own appeal in satisfaction).
Part One! In this episode, our bumbling protagonist figures out their method, makes PHP installation reparations, realises the obvious, and deals with pestilential incompatibilities!
I started with a few things: one, I had an MS SQL (2008) server already up and running on Server 2k8 R2. IIS 7 is running on the server, since it hosts other webpages too. The database is maintained by someone else (let’s call ’em the DBA), who supplied the query string that I would need for the results we wanted to display. The query and resulting output are a fairly simple affair, being a select statement where the user supplies one variable. A single row, or nothing, is returned.
With server and query settled, I needed to know how to use a webpage to make said query to the database. I know enough HTML to build a basic page layout, and thanks to places like Codecademy and HackerRank and others, I felt like I knew enough to learn what else I needed. Somehow, I ended up at the conclusion that I would build an HTML page and use PHP to connect to the database, make the query, and display the result. Great, hurrah, all that. Many sites gave their input to this decision, like this one and especially this one and kind of this one. I figured it couldn’t be so hard, I’d seen that syntax before…
Still in familiar territory, I set up a new site in IIS, under
C:\inetpub\wwwroot\, and had it listen on some port. I thought I’d whack a quick PHPInfo page together to make sure the web-server was A-OK before I moved on. Turned out it wasn’t. I have no idea why (I inherited this IIS host), but the installation of PHP on this machine was broken all to hell. It had been installed to a drive that no longer existed on the host. Alright, I would reinstall PHP. Luckily, a very helpful How-To Geek page led me through it [a note on this, if you are installing PHP too – be sure to also grab the appropriate Visual C++ redistributable from the PHP download page]. Great, with that done, my PHPInfo page was working and I could get to work on actually getting this webpage together…
I threw a first iteration of the page together – intended to check that my connection to the database, using the
mysql_connect extension/API, was fine. Just a quick case of “if the connection is successful, print a message.” I was greeted by an infuriating “500 internal server error”. Firewall’s were okay, the installation seemed fine, I pored over config files for some option I’d missed, repeated installation steps, PHP and IIS logs didn’t indicate anything – no, as far as I could tell, everything was set up as it should have been.
Turned out I missed something glaringly, painfully obvious: mysql_connect is deprecated. Initially that piece of info did not help me; it just led to me trying its replacements, to the same result. Eventually, the magical half-brick of realisation crashed through the frosted glass of my test chamber: I have an MS SQL server, not a MySQL server (oh, exasperation!). I needed sqlsrv_connect.
This revelation was not without its drawbacks, however, and I needed more software. That included the Microsoft Drivers for PHP for SQL Server (version 4.0 for me, ’cause I’m using PHP 7.0+), and the Microsoft ODBC Driver 13 (or 11 would work too, I guess). So that was nic- actually, wait, what? Oh. A bug. How unexpected. Turns out the download page for the MS Drivers for PHP for SQL Server was really quite serious about that 7.0 thing. Naturally, like a fool, I picked the latest stable PHP release when I first installed it two paragraphs ago, and this was a mistake. Version 4.0 of the PHP drivers are incompatible with PHP 7.1+ (and as of this writing, the working preview update mentioned in that GitHub issue I linked has not yet made its way to the full binary release). So, I grabbed PHP 7.0 (non-thread safe) and replaced my PHP 7.1 install (again with the help of the How-To Geek article I mentioned earlier).
Finally, I had an environment in which I could build this blasted page…
Environment and build preparations complete, we must stumble on in our adventure to Part Two! Will our queries be displayed?! Find out!