Installing Microsoft SQL Server 2008 Drivers for PHP

Installing the Microsoft Drivers for SQL Server 2008 is a not as simple as one would think. One of the things that had me confused was the fact that I had previously made a conection to SQL Server 2005 with no problem. Now, using SQL Server 2008 (Express) nothing worked.

After some research I realized the problem was tha in my previous connections I was using PHP 5.2.x which used the mssql driver. For some reason the mssql server is no longer supported for PHP 5.3 and we are supposed to use the Microsoft Driver.

Installation

Download the drivers from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=80e44913-24b4-4113-8807-caae6cf2ca05&displaylang=en .

When you install you´ll need to choose which dlls to use. Use the following table to decide:

PHP Version	Web Server	Dlls
5.2	IIS	php_pdo_sqlsrv_52_nts_vc6.dll php_sqlsrv_52_nts_vc6.dll
5.2	Apache	php_pdo_sqlsrv_52_ts_vc6.dll php_sqlsrv_52_ts_vc6.dll
5.3	IIS with FastCGI	php_pdo_sqlsrv_53_nts_vc9.dll php_sqlsrv_53_nts_vc9.dll
5.3	Apache as mod_php	php_pdo_sqlsrv_53_ts_vc6.dll php_sqlsrv_53_ts_vc6.dll
5.3	Apache as FastCGI	php_pdo_sqlsrv_53_nts_vc6.dll php_sqlsrv_53_nts_vc6.dll

Double click on SQLSRV20.EXE

Click yes.

Select your output directory and click Ok.

Click Ok.

Copy the file c:\temp\php_pdo_sqlsrv_53_ts_vc6.dll and c:\temp\php_sqlsrv_53_ts_vc6.dll to your PHP_HOME\ext folder (since I´m using WAMP my path is C:\wamp\bin\php\php5.3.0\ext)

Edit your php.ini (mine is at C:\wamp\bin\php\php5.3.0\) adding the following lines

extension=php_sqlsrv_53_ts_vc6.dll

extension=php_pdo_sqlsrv_53_ts_vc6.dll

If you´re using WAMP you have to edit the php.ini that Apache uses. Double click on php.ini add the extensions and restart Apache.

Testing Installation

Open command prompt a run

php –v

If you get an error like this one:

You´re using the wrong dlls for your php installation. Verify you´re using php_pdo_sqlsrv_53_ts_vc6.dll and php_sqlsrv_53_ts_vc6.dll

Create a directory c:\temp\sqlservertest.

In Apache create an alias for this directory with the name sqlservertest.

Create a file in c:\temp\sqlservertest with the name phpinfo.php and the following content:

<?php phpinfo(); ?>

We had already created a Table on SQL Server called users to test the driver.

Create and index.php file write the following code.

<?php
        /*
         * Specify the server and connection string attributes.
         */
        $serverName = "MYMACHINE\SQLEXPRESS";
        $uid = "username";
        $pwd = "password";
        $database = "sqlservertest";
        try {
        $conn = new PDO( "sqlsrv:server=$serverName;database=$database", $uid, $pwd);
        $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $conn->setAttribute(PDO::SQLSRV_ATTR_DIRECT_QUERY , true);

        echo "Connected to SQL Server<br /><br />\n";

        $sql = "select * from myschema.users";
        $results = $conn->query( $sql );
        outputRows($results);
       
// Free statement and connection resources.
        $stmt = null;
        $conn = null;
        } catch( PDOException $e ) {
        echo "<h1>Error connecting to SQL Server</h1><pre>";
        echo $e->getMEssage();
        echo "</pre>";
        exit();

        }
        function outputRows($results)
        {
        echo "<table border='1'>\n";
        while ( $row = $results->fetch( PDO::FETCH_ASSOC ) ){
        echo "<tr><td>{$row['id']}</td><td>{$row['firstname']}</td><td>{$row['lastname']}</td>\n";
        }
        echo "</table>\n";
        return;
        }

References

http://www.php.net/manual/en/ref.pdo-dblib.php

http://www.ditii.com/2010/11/16/sql-pdo-and-microsoft-sql-server-whitepaper-php-drivers-for-sql-server/