There are several methods of getting a PHP-enabled web server talking to your Microsoft SQL Server database. Depending on your requirements and available PHP versions, extensions, though, it can be a tedious process. Effectiveness of a particular solution may vary based on versions of SQL Server and PHP. There are options, though, and I’ll detail a few methods I’ve used to get PHP working with MSSQL. Hopefully one of them will work for you.

First, I’d like to plug a really great software called WAMP Server. WAMP is a pre-packaged Apache Web Server, MySQL Server, and PHP installation. It’s intended for development purposes, but it can just as easily be sat on a production box after some minor configuration changes. The neat feature in WAMP is that you can download additional versions of PHP; switching between versions is easily done via right-clicking an icon in the Windows system tray. Additionally, PHP extensions can be toggled on or off from the same menu. That’s a big deal if you want to make sure your code works in PHP 5.1, 5.2 and 5.3, or even check PHP 4 support. That said, XAMPP is a similar tool that has all of the functionality of Apache, MySQL and PHP bundled up, but it’s a little less easy to drop in support for separate versions of PHP.

Note that both of those software come packaged with MySQL Database Server. The point here is to use MSSQL, so you’ll probably want to disable the mysqld service.

Option 1, mssql

PHP actually has a pre-bundled extension mssql or php_mssql. This gives the developer the ability to connect to a database, run queries and return query results. Very nice, if you can get it to work. I’ve found that recent versions of PHP running on WAMP (specifically 5.2.9, 5.3.1, 5.3.4, 5.3.5) simply will not connect to my locally-installed MSSQL Server running 2008. Some versions don’t appear to have the php_mssql extension packaged. I don’t really know if this is a failure to compile things from the distributors of WAMP or if there’s a more-upstream issue, but it doesn’t work. What does work for me is WAMP and PHP Version 5.2.11. Grab it from WAMP’s Sourceforge Page.

Switching to PHP 5.2.11 and enabling the php_mssql extension via the UI is all I had to do to make my mssql_connect and mssql_query functions work. The down-side is that I’m stuck in PHP 5.2 and miss out on some of the cool new functions in 5.3.

I have probably 10 different versions of PHP installed on my machine, and 5.2.11 is the only one that works. Your mileage may vary, I’m guessing drastically based on your version of SQL Server.

Option 2, ODBC

ODBC support is built in to every version of PHP since PHP4. It’s basically a standardized way of connecting to and working with databases, and it works great. The down side of ODBC is that it’s said to be slower than the new OLE-DB. I maintain one PHP-based portal using ODBC and customers do not complain; like everything else, performance very depends on your resources and requirements.

Using ODBC, you’ll have to specify a specific connection string. Here’s a quick way of doing that:

function db_connect($host,$user,$pass,$db){
	// This connection string is used on the production box, Windows Server 2003
	// and SQL Server (hosted spearately but also Win 2003) version 2005
	$dsn = "Driver={SQL Native Client};Server=$host;Database=$db; Uid=$user;Pwd=$pwd;";
	// This is a more generic one I use with to dev with, Windows 7 and SQL Server 2008
	$dsn = "Driver={SQL Server};Server=$host;Database=$db"; // only for dev. purposes

	try{
		$conn = odbc_connect($dsn, $user, $$pass);
	}catch (Exception $e){
		die('Could not connect to database.');
	}
	return $conn;
}

Option 3, OLE DB and/or Application-Specific Providers

I recently worked with a Sage SalesLogix portal that needed to take advantage of some application-specific functions within an OLE DB Provider shipped from Sage. I was capable of using the MSSQL database functions directly with ODBC or mssql with above methods, but to access the extra functions I had to use their provider. This can only be done using Windows boxes because we must leverage the PHP COM Class. This works similarly to the ODBC connection but methods’ names are a bit different and, in my opinion, less intuitive. Here’s a sample from my database-connecting class that used an OLE DB-based COM Connection.

class oledb_database{
	var $user;
	var $pass;
	var $db;
	var $connection_string = 'Provider=SLXOLEDB.1;Data Source=localhost;Initial Catalog=$db;User Id=$user;Password="$pass";Persist Security Info=True;Extended Properties="PORT=1706;LOG=ON;"';
    var $rows_count;

    function connect(){
        if(!class_exists('COM')){
            die("COM Class could not be found. Please run system from a Windows-based PHP system.");
        }
        $conn = new COM("ADODB.Connection") or die("Cannot start ADO");
        global $config;
        try {
            $conn->Open($connection_string);
            $this->link = $conn;
            return true;
        } catch (Exception $e) {
            echo 'Caught exception: ',  $e->getMessage(), "<br>\n";
            return false;
        }
    }
    function query($query){
        try {
            $this->result = $this->link->Execute($query);
            $this->rows_count = $this->result->Fields->Count();
            return $this->result;
        } catch (Exception $e){
            echo 'Caught exception: ',  $e->getMessage(), "<br>\n";
            echo "<h3>$query</h3>";
            die();
        }
    }
}

The OLE DB method felt much more cumbersome, but in the end it had nice exception handling and worked fine (once I got the connection string right).

Conclusion

Your options are native mssql support if you can manage it, classic ODBC and or modern OLE DB connections. Surely one of these methods will allow you to connect and work with your data.

There is one final method for connecting to MSSQL databases that I am aware of, and it is FreeTDS. This is really a project geared to getting MSSQL connectivity supported in Linux-based Apache systems and is known to be difficult to work with. If you must use a Microsoft SQL Server database, you will have a easier time with things if you work with a Windows-based Apache and PHP configuration with one of the above methods.