Handling multiple MySQL results in PHP

Back in the day (June of 2007 to be a little more exact) I posted a little snippet about using PHP to hand multiple MySQL query results with the MySQLi extension. At the time it was something I was using just to see what was coming back from the database when I called a stored procedure that happened to have multiple results in it. Skip to how this really works…

Since then I have improved my little handler, added some library code to actually handle queries and results (and timers and requests and… dude, so much). The new code, which is much fatter (not necessarily better, just fatter in that it has a lot more features in it), now encapsulates much of what the original, simple script was doing. Unfortunately, the script is no longer as simple as I wanted it to be. However, it does now come in several files which might be of use to you in some of your own code.

Because of the change that I made to the way this code works, I have decided to package it into a little zip file for your downloading pleasure. The zip file includes:

  • index.php (the actual procedure/result tester)
  • A lib directory, which contains:
    • memorystats.php (A memory use and reporting object)
    • mysql.php (The database connection and query object)
    • mysqlresult.php (The database result object)
    • request.php (The HTTP request object)
    • timer.php (The process timer object)

It should be ready to use right out of the box, with the exception of making sure to add your own username, password and database name to the $db->connect() call (at about line 85 in index.php). I’m sure you could expand it to be more of what you want in a tester (like adding a database chooser to it – right now I have a database server chooser, but not a database chooser within a server) but overall it is a very nice little script that does pretty much what it was built to do: run a query/procedure and return all of the results in it.

Please note: This is seriously not something that you should think to use in a production environment. The farthest this has ever made in any of my architectures is the dev machine. Not that the code is not good. It is just that putting something that has free form access to your database onto a production machine is, in my opinion, a remarkable stupid thing to do and I would hate to have my name associated with code that was used to exploit your business because you chose to put a huge gaping hole into your network through a simple little test script. Just sayin’.

Under the hood
Before diving into that mash of code I included in the zip file, it might be a good idea to see just the relevant code so you can, if you want to, just tap into the multiple result set handling of MySQL results in PHP. Without further ado…

<?php
// Set a query
$sql = "SHOW TABLES FROM `mysql`; SELECT VERSION() AS `version`; SELECT NOW() AS `date`;";
 
// Change the params to you own here...
$mysql = new mysqli('HOSTNAME', 'USERNAME', 'PASSWORD', 'DATABASE');
 
if (mysqli_connect_errno()) {
	die(printf('MySQL Server connection failed: %s', mysqli_connect_error()));
}
 
// Initialize our result set array
$results = array();
 
/** 
 * Check our query results
 * 
 * This is where the magic happens, and it must be this way anytime you use a
 * stored procedure. The reason for that is the MySQL server always sends a 
 * status return with any query, even a select query. That means that even a
 * single result set select query will return two results. Those results will
 * not necessarily make it to your application (I think the client handles 
 * that) but in my experience I have always had to use multi_query to get this
 * to work.
 */
if ($mysql->multi_query($sql)) {
	do {
		// Create the records array
		$records = array();
 
		// Lets work with the first result set
		if ($result = $mysql->use_result()) {
			// Loop the first result set, reading the records into an array
			while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
				$records[] = $row;
			}
 
			// Close the record set
			$result->close();
		}
 
		// Add this record set into the results array
		$results[] = $records;
	} while ($mysql->next_result());
}
 
// Close the connection
$mysql->close();
 
// Set our output type
header('Content-Type: text/plain');
 
// What do we have
print_r($results);

And there you have it. Nice, simple, clean and easy to use all by itself.

Building the PHP MS SQL Server extension from source on Ubuntu 8.10

Yesterday I wrote about how I got an enhanced Sybase CT extension for PHP built on Ubuntu 8.10 from source files and from Red Hat RPM archives. Today I wanted to write about installing the SQL Server extension from source on Ubuntu 8.10.

I have written about this once before but it was for a Red Hat environment. Seeing as we are converting all of our system at work to Ubuntu I had to do the same thing for the Ubuntu platform. And wouldn’t you know it, Debian and Red Hat are vastly different from each other.

Aside from the stupidity that is the Ubuntu package manager’s rendition of the SQL Server extension for PHP the fact remained for me that I had to be able to integrate database communication from LAMP to a SQL Server on Windows as well to a Sybase server on Unix. The Sybase bit was taken care of in yesterday’s write up. But how do we get PHP to talk to SQL Server on Ubuntu 8.10?

Throw out the notion that you can use the php-sybase package from the package manager. It munges stuff up pretty badly and at the same time does things to the Sybase extension that make it unusable for my needs. Not to mention that if their is a Sybase extension already installed then it really pukes because the php-sybase extension that installs the SQL Server functions actually installs the mssql_* functions then maps the sybase_* functions to them, which makes PHP squawk since those functions are already defined.

So the only way to make this happen is to build the SQL Server extension from source.

Building and installing the mssql extension from source

The follow steps will guide you through how I got the mssql extension built and installed on Ubuntu 8.10:

  • Build the FreeTDS library
    FreeTDS is a free, open source library of clients that allow Linux machines to talk to SQL servers and Sybase servers. Download the latest stable version of the FreeTDS library (as of this writing it was 0.82) and unpack the source to a directory on your machine. Once the source is unpacked, change to the directory where the source is located and configure and make the library:
    $ ./configure --prefix=/usr/local/freetds --enable-msdblib
    $ make
    $ sudo make install

    DO NOT MAKE CLEAN AT THIS POINT.

    Before you do anything else you must make sure that you have copied over all the necessary files from FreeTDS. “Wait, didn’t the installation routine do that?” you might ask. No, it doesn’t, because of a change made on the part of the FreeTDS developers. There are two files that are needed in order to build the mssql extension. Those files are among the make files and must be copied over to the freetds install directory.

    From inside the source directory of freetds, where you built it from, enter:
    $ sudo cp include/tds.h /usr/local/freetds/include
    $ sudo cp src/tds/.libs/libtds.a /usr/local/freetds/lib

  • Get the PHP source code
    Change to a directory where you wouldn’t mind having the entirety of the PHP source stashed. From the command line type:
    $ apt-get source php5
  • Copy the mssql extension files to a directory you can build from
    My preference is to leave source alone and work from copies. So I always copy the files I need to a different location and build from the copies. To that end, I created a directory at ~/SourceCode/php-mssql and copied the following files from the original PHP source directory:
    $ cp ext/mssql/config.m4 ~/SourceCode/php-mssql
    $ cp ext/mssql/php_mssql.c ~/SourceCode/php-mssql
    $ cp ext/mssql/php_mssql.h ~/SourceCode/php-mssql
  • Make and install the extension
    Make sure you have the php5-dev package installed on your system so that you can build PHP extensions from source. From inside the directory where your mssql extension source code is, at the command line, enter:
    $ phpize
    $ ./configure --with-mssql=/usr/local/freetds
    $ make
    $ make install

    Find out where your extensions directory is on your machine and quickly check it to make sure there is a php_mssql.so file living in it. On my machine the extension directory is /usr/lib/php5/20060613+lfs/. Yours may be different.

  • Configure PHP to use the new extension we just made
    Now we need to tell PHP to use the new extension we just built. To do that we need to create an ini file for the extension and put it inside of the extensions directory where PHP can find it. On my machine PHP looks for ini files to parse in /etc/php5/conf.d/ so naturally that is where I am going to go to to tell PHP to use this extension.

    $ cd /etc/php5/conf.d/

    Now we need to create an ini file and put into a directive to load the extension. You can use whatever editor you like. I prefer to use vim:
    $ sudo vim mssql.ini

    Inside this file place the following two lines:
    ; Enable the mssql extension
    extension=mssql.so

  • Configure your environment to load a much needed environment variable whenever the machine starts
    Much like the Sybase extension we did yesterday the mssql extension needs an environment variable in order to function properly. Again, this one caused me fits for a long time in Ubuntu. To be sure you can use the SQL Server extension from both the CLI and the web server you will need to add an environment variable to both the /etc/profile startup script AND the web servers environment variable setting script.

    $ sudo vim /etc/profile

    At the end of the file add:
    export FREETDSCONF=/etc/freetds/freetds.conf

    Now add this same entry into your web server’s environment variables. I am using apache and assuming you are to. If not, consult your web server’s documentation for how to do this:
    $ sudo vim /etc/apache2/envvars

    Now add these entries to the end of the file:
    export FREETDSCONF=/etc/freetds/freetds.conf

  • Configure FreeTDS
    In order for FreeTDS to communicate properly with the SQL server a DSN of sorts needs to be created. To make a DSN you need to edit the freetds.conf file:
    $ sudo vim /etc/freetds/freetds.conf

    Go to the end of the file and add the following lines (MAKE SURE TO INDENT ALL OF THE CONFIG VALUES FOR THE SERVERNAME):
    ;--- Custom MSSQL server name ---
    ; THIS CAN BE ANY NAMED SERVER REFERENCE YOU WANT TO CREATE
    [SERVERNAME]
      ; This is the host name of the MSSQL server
      host=HOSTNAME
      ; This is the port number to use
      port=PORTNUMBER
      ; This is the TDS version to use for anything over Server 2000
      tds version=8.0

    Now we need to add the freetds library to the load library stack.
    $ sudo vi /etc/ld.so.conf

    Go to the end of the file and add the following line:
    /usr/local/freetds/lib

  • Restart your web server
    Like everything that involves a change to the PHP environment or configuration on your machine, restart the web server. I am assuming this is being built upon an apache server. If not, you will need to know how to stop and start your web server or, at the very least, know how to reboot your machine:
    $ sudo apache2ctl stop
    $ sudo apache2ctl start

    Run a PHP info page or CLI call to see if it is loaded:
    $ php -m

    You should now see mssql.

Again, I hope this was helpful to you. If it was, please leave a comment and let me know. It something went haywire for you, leave me a comment, too. Systems being what they are, it is never unheard of for two almost identical systems to have vastly different experiences with building software from source.