Hello everybody!
All right?
In this post, I would like to demonstrate how to connect to SQL Server using PHP (Xampp) and PDO driver on Windows. Many people find it difficult to install and configure the drivers due to small technical details that go unnoticed and make it impossible to connect PHP to the SQL Server database.
The first step to getting this connection is download Microsoft® ODBC Driver 18.2.1.1 for SQL Server® to your machine and perform driver installation.
The next step is to view the information of your environment. To do this, create a file called phpinfo.php in the root of your PHP web server (default directory is C: \ xampp \ htdocs), with the content below:
| 1 2 3 | <?php phpinfo(); ?> |
After viewing this file in your web browser (http: //localhost/phpinfo.php), you should see a screen like this:
In this screen, we need to identify the following environment variables:
- PHP version (in this case, 7.1.1)
- PHP architecture (in the example case, x86)
- Configuration File Location (php.ini)
- Check if PHP build version is thread safe (TS) or non thread safe (NTS) (in this example, it's TS)
After identifying this information, let's now download Microsoft Drivers for PHP for SQL Server, according to your version of PHP:
Relationship between driver version and PHP version
| Driver Version | PHP version | Driver Download |
|---|---|---|
| 5.11.0-beta1 | PHP 7.4 to PHP 8.2 | Download |
| 5.10.1 | PHP 7.4 to PHP 8.1 | Download |
| 5.9 | PHP 7.3 to PHP 8.0 | Download |
| 5.8 | PHP 7.2 to PHP 7.4 | Download |
| 4.3 | PHP 7.0 and PHP 7.1 | Download |
| 4.0 | PHP 7.0 | Download |
| 3.2 | PHP 5.6.4 + or PHP 5.5.16 + or PHP 5.4.32 | Download |
| 3.1 | PHP 5.5.16 + or PHP 5.4.32 | Download |
| 3.0 | PHP 5.4.32 or PHP 5.3.0 | Download |
| 2.0 | PHP 5.3.0 or PHP 5.2.4 or PHP 5.2.13 | Download |
Relationship between driver version and ODBC driver version
Click here to expandRelationship between driver version and database version
Click here to expandRelationship between driver version and Windows version
Click here to expandAfter identifying the correct driver version according to your version of PHP, SQL Server, and Operating System, download the indicated driver and choose a folder to extract the files to during installation.
After that, copy all the files that were installed to the directory. php \ ext on your Xampp installation (default directory is C: \ xampp \ php \ ext)
If you do not know the path of your configuration file (the default path is C: \ xampp \ php \ php.ini), in the phpinfo () screen shown above, you can identify the correct path of your php file. ini looking for Loaded Configuration File
After copying all files, edit the php.ini file from your installation and add the following records:
extension=php_pdo_sqlsrv_71_ts_x86.dll
extension=php_sqlsrv_71_ts_x86.dll
Remembering that the name of the DLL's varies according to the version of PHP (71, 70, 54, etc.), Thread-safe (TS or NTS) and architecture (X86 or X64) and must ALWAYS be the same as the name of the files you downloaded and copied it to the php \ ext directory.
Once you have edited the php.ini file, simply restart Apache for the changes to take effect, as shown below:
Open the phpinfo.php file (http: //localhost/phpinfo.php) and see if the driver has now been loaded:
If it is the same as print, it means that the driver is installed and ready to use!
Connection Test Script
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | <?php try { $servidor = "vm-dba"; $instancia = "sql2016"; $porta = 1433; $database = "master"; $usuario = "usuario_php"; $senha = "123456"; $conexao = new PDO( "sqlsrv:Server={$servidor}\\{$instancia},{$porta};Database={$database}", $usuario, $senha ); } catch ( PDOException $e ) { echo "Drivers disponiveis: " . implode( ",", PDO::getAvailableDrivers() ); echo "\nErro: " . $e->getMessage(); exit; } $query = $conexao->prepare( "select @@version" ); $query->execute(); $resultado = $query->fetchAll(); echo $resultado['0']['0']; unset( $conexao ); unset( $query ); |
Result:
That's it!
I hope you enjoyed this article and can now start building applications using PHP and SQL Server!
There is also a very interesting link from Microsoft itself that helps developers create a PHP environment. To access this documentation, click this link here.
Strong hug!
Sensational!!!! You can't imagine how much you helped me, even though it's been a long time since you posted the content
Do you have a tutorial with Xampp on Ubuntu and SQL-Server on Windows?
I'm also looking for a tutorial like that.
Helped me a lot! Congratulations!
I found!
For PHP 7.4? copartilha ai
Has anyone found a solution to connect php 7.4 with sql server?
Best post I found! Congratulations! But I did all the steps and mine does not appear in phpinfo the name sqlsrv. What can it be?
I also have the same problem, did you solve it?
Hello, with 7.3.6 version of PHP, can SQL Server 2008 R2 and Windows Server 2008 R2 make the connection?
Marvin, I believe not. Because on Windows server 2008 I think you can only use up to version 4.0 of the driver, which only supports PHP 7.0…
simply speechless, it was the only one I found that went through the steps step by step.
Thank you, Helium!
I hope it was helpful to you 🙂
Good morning, I did the whole procedure, I put the xampp in the same version and I followed all the steps, however it still presents the error “Drivers available: mysql, odbc, pgsql, sqlite Error: could not find driver”, Could someone help me?
Speak Luiz, how are you?
Have you checked the versions of Windows and PHP? Did you use the correct driver for your version?
Guys, this post is a fantastic walkthrough.
Just a detail, the Ultimate XAMPP doesn't really work. only from 7.2 below.
Hello, in my case, in PHPinfo () the name sqlsrv does not appear in “Registered PHP Streams” and I think that for this reason, the connection test does not work. Does anyone know what can it be?
Hi Diego, did you manage to solve this problem? I am also trying to solve this same problem ...
Just to complement what I wrote before, what I tried to do was install PHP on IIS 7 (hitherto quiet), enable PDO_SQL on Windows 8 R2 and SQL 2008 (that's where I got it!) And also the PHP manager on IIS panel (which only worked right after the right versions installed)
This is one of the most important post I found because it was the only one I objectively found about the wrong version installed on the system. I had solved it the day before, but I stuck for hours and hours until I found out it was a version issue. I can't find this information on the PHP website or microsoft support pages. In my case I found out why in extracting the PDO drivers comes a readme detailing which systems are compatible.
Hi, I installed Xampp on Windows and I would like to know how I manage the apache log file (C: \ xampp \ apache \ logs), my log file is with 50 MB and in future I will have problems, it is possible to delimit a size and after reaching the size, the auto recycle log file?
Another question how do I get the log file (C: \ xampp \ apache \ logs) to register the network user who accesses a page in PHP?
Thank you!
Congratulations!
Only post that can connect to the database.
Thank you!
Sensational!!!