Advertisment

Using PHP With IIS 7 and SQL Server

author-image
PCQ Bureau
New Update

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. 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.

Advertisment

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.

While installing

PHP, make sure you select the IIS FastCGI option.
Advertisment

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.

The “phpinfo()”

page should show the “sqlsrv” section if the extension is installed

correctly.
Advertisment



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.

A page that

connects to SQL Server 2008 and retrieves some data using the SQL Server

driver for PHP.
Advertisment

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.

Advertisment