BANGALORE, INDIA: PHP is one of the most well known and used scripting languages for web applications in the Open Source and Linux world. However, it might be useful to know that you can not only run PHP on Windows servers using Internet Information Services, but also connect to SQL Server for data.

Now why would you want to do that, one may ask given that Windows (and hence IIS) as well as SQL Server are proprietary products. There are a few reasons for this. First off, on Windows Server 2008 using IIS7, PHP applications get access to a number of features that are not available to them on other platforms easily.

PHP tops Ruby Javascript in developer survey

For instance, you can enable features like caching, HTTP redirection, membership and role management and many more IIS features and have them work with PHP natively without changing the PHP code at all. A detailed look at the IIS7 Integrated Pipeline describing this was published in the December 2007 issue of PCQuest.

SQL Server is also a good target for PHP applications as it too offers a number of features that RDBMSs like PostGreSQL and MySQL do not. This includes a higher level of scalability, enterprise class features and query optimizers. You can even get a lot of these benefits with the free SQL Server Express editions as well. So in case you have mission critical applications or wish to make your PHP application target multiple databases, this is something that you can do quite easily.

Coming back to how you can setup PHP on IIS7 and for SQL Server, you need to get the latest version of PHP from either www.php.net or get the installable off the DVD included with this issue. Make sure IIS7 with CGI support is installed on the Windows Server 2008 machine.

Open Server Manager | Roles | Web Server | Add Role Services and check the CGI option and install if not already done so. Now go ahead and install the MSI for the PHP install. Select the IIS with FastCGI support when prompted and install any extensions you wish. Next, open IIS Manager and select the server. On the right pane, open Handler Mappings.

In the rightmost action pane, select Add Module Mapping. In the dialog that comes up, enter *.php for type, FastCGI for module, C:\Program Files\PHP\php-cgi-exe for executable and PHP with FastCGI as name.

Once complete, get the SQL Server Driver for PHP from Microsoft's site or off the DVD included. Run the file and extract the files to a temporary folder. Copy the php_sqlsrv_st.dll to c:\Program Files\PHP\ext. Open the PHP.ini file and add the line extension=php_sqlsrv_st.dll in the Extensions section. Save this and restart the Web server.

You are now ready to check out your PHP install as well as start writing PHP code that works with SQL Server. First off, create a simple text file in Notepad with the following code and save it as test.php as your Web server's web root folder. For instance in c:\inetpub\wwwroot\ php\test.php.

phpinfo();
?>

Open Internet Explorer and point to http://localhost/ php/test.php. You should now see the PHP status information page. Check out whether the extension named sqlsrv is loaded. Now comes the time to actually connect to the database. For this, there is one more optional step that you may need to perform.

In case you are running SQL Server 2005 on the same machine as where the PHP code is running, you need not do anything, but write your code. However, in case you are running SQL Server 2005 on a remote machine or SQL Server 2008 on the same or a remote machine, you will need to install the SQL Server 2005 Native Client on the Web server.

This is because although the MS driver for PHP is optimized to work with SQL Server it works only with the SQL Server 2005 native client. You can download this from the Microsoft site or get it off the DVD. After this, you can start writing code to connect to SQL Server. Take a look at the code below for a small sample.

"AdventureWorks2008", "UID"=>"sa", "PWD"=>"password");$conn = sqlsrv_connect("(local)", $connectionInfo);if( $conn === false ){ echo "Could not connect.
"; die( print_r( sqlsrv_errors(), true));}$tsql = "SELECT ProductID, UnitPrice, StockedQty FROM Purchasing.PurchaseOrderDetail WHERE StockedQty < 3 AND DueDate='2002-01-29'";echo "

Connecting to AdventureWorks2008 on local SQL Server 2008

";$stmt = sqlsrv_query( $conn, $tsql);if ( $stmt ) echo "Statement executed.

";else { echo "Error in statement execution.
"; die( print_r( sqlsrv_errors(), true));}while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC)){ echo "ProdID: ".$row<0>."
"; echo "UnitPrice: ".$row<1>."
"; echo "StockedQty: ".$row<2>."
";}sqlsrv_free_stmt( $stmt);sqlsrv_close( $conn);?>

Save this file as say get.php in the same location and browse over to http://localhost/php/get.php. You should see a few records from the AdventureWorks database being shown. The SQL Server driver for PHP is a great way of connecting to SQL Server databases from PHP. Written by Microsoft themselves, this offers much better performance and stability than stock or 3rd party drivers.

As mentioned, if you have the option of using PHP on IIS7 and SQL Server for a large and scalable application, it makes a good choice to do so and these drivers let you work very nicely with the database. Do note that these drivers are open source and Microsoft has supplied the source at CodePlex where you can download, view and change them if you wish.