Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Jeremiah Shore
Jeremiah Shore
31,168 Points
Posted by Jeremiah Shore
Jeremiah Shore
31,168 Points

How do I connect to Microsoft SQL Server with PHP (and PDO)?

I am trying to connect to a cloud-hosted MS SQL Server (2008 I believe) and I'm struggling to get things working.

I work on Windows 7 computer, which is where I have XAMPP installed, which is currently running PHP 5.6.3.

when execute this code I am getting a driver not found error (filenames and values modified from actual output):

object(PDOException)#2 (8) {
  ["message":protected]=>
  string(21) "could not find driver"
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  int(0)
  ["file":protected]=>
  string(37) "C:\xampp\htdocs\database_test.php"
  ["line":protected]=>
  int(3)
  ["trace":"Exception":private]=>
  array(1) {
    [0]=>
    array(6) {
      ["file"]=>
      string(37) "C:\xampp\htdocs\database_test.php"
      ["line"]=>
      int(3)
      ["function"]=>
      string(11) "__construct"
      ["class"]=>
      string(3) "PDO"
      ["type"]=>
      string(2) "->"
      ["args"]=>
      array(3) {
        [0]=>
        string(64) "sqlsrv:host=SERVER;dbname=DATABASE;port=PORT"
        [1]=>
        string(24) "USER"
        [2]=>
        string(8) "PASSWORD"
      }
    }
  }
  ["previous":"Exception":private]=>
  NULL
  ["errorInfo"]=>
  NULL

I tried installing drivers 'php_pdo_sqlsrv_55_ts.dll' and 'php_sqlsrv_55_ts.dll' into the PHP ext directory and subsequently updated the php.ini file to load them. When I tried to do so I got the following error:

PHP Startup: sqlsrv: Unable to initialize module

Module compiled with modeule API=20121212

PHP compiled with module API=20131226

These options need to match

What am I doing wrong and how do I connect to an SQL databse?

Craig Francis
Craig Francis
4,195 Points
Craig Francis
Craig Francis
4,195 Points

Looks like the .dll files you have downloaded were compiled against the wrong version of PHP.

Not sure about XAMPP, but usually these pre-compiled installers will provide .dll files for you (so you don't need to download them from the internet, which is unlikely to work).

I assume you have already looked at:

http://bendustries.co/wp/setting-up-xampp-to-work-with-mssql/

http://stackoverflow.com/questions/17542494/php-5-4-and-mssql-on-xampp

https://community.apachefriends.org/f/viewtopic.php?f=16&t=49670&p=191610&hilit=SQLSRV

While really not appropriate to your problem, below are my notes (from a few years ago, so not using PDO) for getting it running on a CentOS/RedHat system using FreeTDS:

yum install epel-release
yum install php-mssql

# getsebool -a | grep httpd_can_network_connect
# setsebool -P httpd_can_network_connect 1
# setsebool -P httpd_can_network_connect_db 1

vi /etc/freetds.conf

    Change "tds version = 8.0"
    Add "client charset = UTF-8"

    To debug, use command line php (avoids SELinux), and the following config:

        dump file = /tmp/freetds.log
        dump file append = yes

apachectl restart

And the PHP source code:

<?php

//--------------------------------------------------
// Connect

    if (!function_exists('mssql_connect')) {
        exit('No support for MSSQL');
    }

    $link = mssql_connect($host . ':' . $port, $user, $pass) or exit('Could not connect to server (' . $host . ')');

    mssql_select_db($name, $link) or exit('Could not select the database');

    mssql_query('SET ANSI_WARNINGS ON');
    mssql_query('SET ANSI_NULLS ON');

//--------------------------------------------------
// Databases

    $databases = array();

    $rst = mssql_query('select * from sysdatabases', $link);
    while ($row = mssql_fetch_assoc($rst)) {
        $databases[] = $row['name'];
    }

    print_r($databases);

//--------------------------------------------------
// Query

    $rst = mssql_query('SELECT
                            [ID],
                            [Field],
                        FROM
                            TableName
                        WHERE
                            [Field] = "Value"', $link);

    while ($row = mssql_fetch_assoc($rst)) {
    }

?>