Using PHP with MS SQL Server

Using PHP with MS SQL Server

(Page 1 of 5 )

Now that PHP runs so well on Windows web servers and speaks natively with SQL Server, there's no longer a need to keep PHP and MS SQL Server separate. The benefits of both are available to use. This article provides instructions on how to enable the sybase or mssql modules in your PHP installation and how to use SQL Server with the DB package.

Most of the articles about using PHP for database applications talk about using it in conjunction with MySQL. If they really want to stretch themselves, they talk about PostgreSQL. The business world uses a different standard though, and Microsoft's SQL Server can be found in most corporate data centers.

Traditionally the installation of a Microsoft architecture meant that it was Microsoft and closed source software all the way. If you needed to talk to a Microsoft SQL Server database, you used Microsoft development tools exclusively. Likewise, if you needed the features of PHP, you stuck to open source database engines. Adding MySQL or PostgreSQL to the database server mix made for some interesting programming to synchronize data, and it made life harder for the system administration staff.

Now that PHP runs so well on Windows web servers and speaks natively with SQL Server, there's no need for this unnecessary division. PHP can be a full corporate citizen. I am experienced with development in both Microsoft and open source technologies, and I admit that they both have their benefits and their limitations. Now I can take the benefits of both. I can have the power of SQL Server with a good programming language that I enjoy using.

This article is making the assumption that you are already familiar with writing PHP database applications. It also assumes that you have at least some familiarity with SQL Server.

Using PHP with MS SQL Server - Required Packages

(Page 2 of 5 )

You'll need to enable the sybase or mssql modules in your PHP installation. Most Linux and the common BSD distributions make it an option when installing PHP. If yours doesn't, the steps to get it into place are simple.

The first dependency is freetds, the library that enables access to Sybase and SQL Server databases. You can download it from http://www.freetds.org. It installs like most open source packages, with the cycle of configure, make, make install.

Next you need to build your PHP installation so that it knows about freetds. That's as simple as adding --with-mssql to the configure switches in most places. Freetds installs by default in /usr/local, so you'll probably need to modify the switch slightly to read --with-mssql=/usr/local

Now copy your new binaries into place and activate them in your php.ini file. There is already an entry in the default INI file to enable mssql, and it just needs to have the comments removed. It's usually necessary to restart your web server after adding the module.

You can verify that the module is installed and happy by checking the php_info() command. In the list of modules should be the mssql module. If you're on a UNIX system, especially FreeBSD or OpenBSD, the mssql module often doesn't appear even when you do have access to it. If the Sybase module is active, mssql is working too. Explicitly specifying the --with-mssql option to configure will usually force php_info() to divulge the mssql module.

Using PHP with MS SQL Server - Optional Packages

(Page 3 of 5 )

The PEAR DB package is incredibly useful for the aspiring cross-platform developer. Since using PHP with MS SQL is not only cross platform but cross culture, it makes a lot of sense to use it when you're looking at a PHP/MS SQL combination. DB is one of the core modules included in PEAR, so if you've already installed PEAR you should have DB available.

If you haven't installed PEAR you can get it from http://pear.php.net. Install it in a common folder on your server (or under your server account) and make sure that it's included in the PHP Include path. If you don't have access to the php.ini file, you can just copy the PEAR distribution into the root of your application, although this gets a little cumbersome.

Using DB

DB is a class hierarchy that lets you use different database engines without changing your PHP code. It makes using one engine more or less the same as any other, and saves you needing to learn the peculiarities of any specific engine. Other articles have covered the use of the DB module fairly well, so I won't go into great detail here. There are some things that you should be aware of though that differ slightly from the MySQL usage that you might be familiar with.

First, if you're working on a UNIX system or your windows build uses Sybase libraries instead of mssql, you'll need to specify the port number to connect to. SQL Server by default listens on port 1433. If you're using the windows binary distribution and have enabled the mssql module that ships with it, you don't need the port number unless a non-standard port number is being used.

The second thing is that you need to be aware of the syntax for using a stored procedure. While stored procedures aren't necessary to use SQL Server, they are a very commonly used feature and it would be a shame not to take advantage of the power that they offer. The syntax is "EXECUTE procedure_name @variable1 = 'value1', @variable2 = 'value2'".

Using PHP with MS SQL Server - An Example

(Page 4 of 5 )

Knowing what's necessary to get SQL Server working is a lot less interesting than actually using it. To that end I have provided an example below. I've chosen to illustrate the use of SQL Server with the DB package. It provides a slightly cleaner example, and those who have used DB for other database engines will notice the similarity.

<?php
require_once("DB.php");

$db =& DB::connect("mssql://user:password@host:1433/membership");
if (DB::isError($db)) {
die($db->getMessage());
}
$db->setFetchMode(DB_FETCHMODE_ASSOC);

$res = db->query("EXECUTE member_list");
if (DB::isError($res)) {
die($res->getMessage());
}

echo "<table>n<tr>n<th>Name</th><th>Address</th><th>Email</th>" .
"<th>Expiration</th>n</tr>n";
while($row =& $res->fetchRow()) {
printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>n",
$row["name"], $row["address"], $row["email"],
$row["expiration"]);
}
echo "</table>n";

?>

There's nothing terribly complicated going on here. It's the sort of nine to five script that junior programmers use to put bread on the table, and that's the point. Using SQL Server is just like using MySQL, PostgreSQL, or even SQLite. The only real difference is the connection string and some of the SQL syntax.

If you're not familiar with the DB extension, I'll give you a quick overview of what's going on. We start with the DB::connect method. This method returns an instance of a DB descended class. Which class will depend on the database engine that you use, but the selection is transparent to you, since all descendant classes support the same core set of methods.

The setFetchMode() method is used to tell the class what format I want my result sets in. In this case I want associative arrays. The default is numerically indexed arrays. I almost always prefer an associative array when it's available, but it's mostly a matter of personal preference in a script this simple.

The query() method takes a query and returns a result set. Having my result set in hand, I use the fetchRow() method until there's nothing more to fetch, processing each row in turn.

One clever little bit of the DB classes is that they return an error object if there are any problems. This saves the sometimes problematic matter of checking return values which have different meanings depending on their context. The universal getMessage() method saves having to look up error codes and generate human readable results.

Using PHP with MS SQL Server - Why SQL-Server?

(Page 5 of 5 )

Microsoft is frequently jeered in the community of open source developers, and a lot of it has been well deserved. For all their bad press though, they do have some products that show they've got developers firmly in mind. One of them in SQL-Server. It has excellent performance and a great feature set, including stored procedures, views and sub selects. It also has a first rate graphical client that makes it one of the easiest database engines to manipulate.

The other reason to consider a marriage of PHP and mssql is the ubiquity of SQL Server in the corporate world. While the open source database engines are excellent, it doesn't make sense from an administrative standpoint to add an additional database server to support the web site, when SQL Server can already take care of that.

PHP is an easier sell to management if it can take advantage of your existing computing infrastructure. It makes management look good, because their choice of database server is being validated, not challenged. It also saves a buck or two, and the manager who doesn't want to make his budget stretch even further hasn't survived an annual review yet.

The fact that SQL Server is so common in business means that it's where you're going to find most of the really interesting data for web applications. Making the case for PHP means getting to that data with a minimum of fuss and delivering results quickly. PHP is a very fast language for development, especially when compared to the alternatives. Combining this development speed with direct access to the corporate data is bound to make the enterprising computer jockey look good in the eyes of management. Likewise, your boss looks good to his boss, and there's nothing like making your boss look good to help your own career.

Conclusions

With the ability of PHP and SQL Server to communicate natively, there's no reason why software development has to be split up into all open source or all Microsoft camps. It's now possible to work with the best tools that both sides have to offer. Managers and accountants will be thrilled to see that projects are being completed with less expense and less system administration overhead. Programmers will be happy to see a wider range of language choices.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.