Connecting php 7.2 to MS SQL using sqlsrv

Question

I'm trying to get a connection to MS SQL up and running via PHP on my machine. I'm running IIS, have PHP 7.2 installed and MS SQL Express 2017. I have my basic web page running but when I click to open the PHP page, the connection does not work.

session_start();

echo "Hello ";
if (isset($_POST['submit'])) {
    $_SESSION["server"] = $_POST['server'];
    $_SESSION["database"]= $_POST['database'];
    $_SESSION["username"] = $_POST['username'];
    $_SESSION["password"] = $_POST['password'];

    echo $_SESSION["database"];

    //CONNECTION
    $serverName = $_SESSION["server"];
    $connectionInfo["Database"] = $_SESSION["database"];
    $connectionInfo["UID"] = $_SESSION["username"];
    $connectionInfo["PWD"] = $_SESSION["password"];

    echo "midway";

    $conn = sqlsrv_connect($serverName, $connectionInfo);

    echo "Bye";
}

When I run this I get "Hello dimensions midway" which suggests the page is working until it hits the connection line.
I am not sure what sqlsrv extension to use.
I have looked around and can see versions for 7 and 7.1 but not for 7.2.

I have added in extension=php_sqlsrv_71_nts_x86.dll to the bottom of php.ini (and the file exists in C:\Program Files (x86)\PHP\v7.2\ext).

Any pointers would be gratefully received. I've spent a couple of days on this and don't think I'm getting anywhere.
Thanks

Solution 1

I faced the same issue. Later I found that I was using older version of Microsoft SQL files for PHP.

For PHP 7.2 you have to use 7.2 version of MS PHP SQL

That version is not available through microsoft website. I found it here https://github.com/Microsoft/msphpsql/releases

Once I used those files everything works fine. I used 64 bit files.

Then you need to add these lines in the php.ini file

extension=php_sqlsrv_72_nts.dll
extension=php_pdo_sqlsrv_72_nts.dll 

Solution 2

What do you need to determine are

• Which version of PHP
• The runtime you are using is Thread Safe or Non-Thread Safe
• The runtime you are using is 64 bits or 32 bits

Then you must look at the requirement page here

Check the version of PHP you are using, so prefer to your question, it's mean to PHP 7.2 which SQL Server extension version 5.3 is the best fit.

Then go to download page, select the link which specific with version you prefer. (in this question is 5.3)

The file you downloaded will be a self-extractable EXE file, then extract to the place where you want.

you will see list of dll files like these...

php_pdo_sqlsrv_7_nts_x64.dll
php_pdo_sqlsrv_7_nts_x86.dll
php_pdo_sqlsrv_7_ts_x64.dll
php_pdo_sqlsrv_7_ts_x86.dll
...
php_sqlsrv_72_ts_x86.dll

What is the meaning that is in the name of these files?

Like I said before, what you need to determine to select the proper extension file. The SQL Server extension need 2 files of the name start with php_pdo_sqlsrv_xxx and php_sqlsrv_xxx.

Next number mean to your PHP version, in this case PHP 7.2, so you need to choose file name has _72_ in its.

Next number mean to your runtime Thread Safe or Non-Thread Safe, which specific in the file name _ts_ (Thread Safe) or _nts_ (Non-Thread Safe).

Next number mean to your runtime 32 bits or 64 bits, which specific in the file name _x86 (32 bits) or _x64 (64 bits)

So check your PHP environment by phpinfo(), or run the command php --version on your console, and then notice the message displayed, for my computer it look like these...

PHP 7.2.12 (cli) (built: Nov  8 2018 06:12:12) ( ZTS MSVC15 (Visual C++ 2017) x86 )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies

By look from those information, I got PHP 7.2, ZTS mean to Thread Safe (Non-Thread Safe will be NTS), and the x86 is 32 bits.

So, what I must pick is the extension which are php_sqlsrv_72_ts_x86.dll and php_pdo_sqlsrv_72_ts_x86.dll

Final: copy them to your /php/ext folder and edit php.ini with those 2 files name.

// example with my case
extension=php_pdo_sqlsrv_72_ts_x86
extension=php_sqlsrv_72_ts_x86

Then save php.ini file and then restart your server.

Test your result by looking at phpinfo.php, which MUST has pdo_sqlsrv in the page. That's mean your environment can run PHP and connect to SQL Server now.

Solution 3

I solved it ~ PHP Version 7.2.4

pdo_sqlsrv : 5.3.0+11108

1.download the proper version sqlsrv and sqlsrv pdo

2.put it into XAMPP\PHP\ext folder

3.after that write the line into php.ini > module setting

extension=php_pdo_sqlsrv_72_ts.dll

extension=php_sqlsrv_72_ts.dll

4.let's make a test for MSSQL still there is a error msg for recommend you to download ODBC Driver for SQL

5.Go to https://docs.microsoft.com/zh-tw/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017 then download the proper file

(mine is Command Line Utilities 11 for SQL Server® X86)

6.fire the test.php again everything works!

Solution 4

I finally found the solution. All you have to do is use these extensions in php.ini file.

php_sqlsrv_72_ts_x86.dll

php_pdo_sqlsrv_72_ts_x86.dll

and restart your XAMPP.

P.S. "I am using PHP 7.2.5 "

View more solutions

Comments

[aslamdoctor]

Hey @Natta, Can you send me the dll files you used? I have the exact same version and I am trying to download the drivers from this Git repo but it is not working github.com/microsoft/msphpsql/releases/tag/v5.3.0

[aslamdoctor]

Thanks for quick reply, I figured out already. I had wrong version of odbc driver.

[LatentDenis]

This is so incredibly badass. You saved me so much time! Thanks for the in-depth explanation.

[Starwave]

Same version, did exactly as you told, php -v runs successfully, meaning it found the dlls, however in last step phpinfo() does not find anything related to pdo_sqlsrv

[Starwave]

False alarm, turns out I was restarting Apache server, but I needed to restart node server >.<