Connect php to sql server xampp

Hiren’s Technical Blog

I am a developer and I know how hard it is to solve difficult issues while programming in case you did not get any help. I have faced this so many times. That’s why I started this blog so that I can share problems and solutions with developer community. In case if you face any issue in programming do check my blog once, it will surely save your time.

Pages

Wednesday, January 25, 2012

Connect with Microsoft Sql server from PHP in xampp

Recently I was working on a project in which requirement was to get data from Microsoft Sql Server from a PHP script. So this blog is about accessing MS Sql server from PHP using PDO object.

First thing is to add necessary extensions. Go to following site and download drivers for MS SQL server.

Once you extract it it will have number of dll files Now you have to identify correct DLL files for your system. This drivers are compatible with PHP version 5.3 . Now go to your XAMPP installation and search for php.dll
It will display correct PHP dll you have.

1) If you have php.dll then move following files to xampp/php/ext directory.

above files should be used if your PHP version is compiled with Visual C++ 9.0 . Else following files should be used.

1) If you have php.dll then move following files to xampp/php/ext directory.

Normally it should be VC 9 files.

Now we have to load files that we added recently. Open the php ini file and add entry in the area of dynamic extensions as follow.

extension=php_sqlsrv_53_nts_vc9.dll
extension= php_pdo_sqlsrv_53_nts_vc9 .dll

Save the ini files and restart you Apache from either XAMPP control panel or from Windows Services Console.

After that you can check php info as follow.

Check the pdo support. It should display sqlsrv now. That means modules are loaded successfully and drivers are available now. Use following code to connect to MS SQL server using sqlsrv

$serverName = » serverName\instanceName «;
$connection = array( «Database»=>»dbName»);
$conn = sqlsrv_connect( $serverName, $connection);

if( $conn ) echo «Connection established.
«;
>else echo «Connection could not be established.
«;
die( print_r( sqlsrv_errors(), true));
>
?>

That’s it and you should be able to query Microsoft Sql Server now. Hope this helps you. This works with Microsoft Sql Server 2005, 2008 and 2008 R2.

31 comments:

Nice article, I read the documentation from microsoft and couldt’n find exactly which dlls to load if I just want pdo support. Thanks to your article I did what I needed. Reply Delete

THANK YOU VERY MUCH FOR YOUR POST!:)))))
We’ve been searching since yesterday and this was the only one that saved us!:)))))) Reply Delete

i got an error This extension requires the Microsoft SQL Server 2012 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86 .

sorry. but i can’t find «php.ini» file ?
and i don’t understand what’s this paragraph mean:
«Now we have to load files that we added recently. Open the php ini file and add entry in the area of dynamic extensions as follow.» ?
thanks in advance,, Reply Delete

it will be in php folder of xampp installation. You must load new extensions by specifying names in php.ini file. Delete

i get an error when i want to connect server. this is an error :

Array
(
[0] => Array
(
[0] => 28000
[SQLSTATE] => 28000
[1] => 18456
[code] => 18456
[2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.
[message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.
)

[1] => Array
(
[0] => 28000
[SQLSTATE] => 28000
[1] => 18456
[code] => 18456
[2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.
[message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.
)

I have installed SQLSRV20.EXE for my Win XP and MS SQL 2008 R2 in the ext directory. I can not find php.dll file anywhere. Modified the php.ini file as instructed. When I restart Apache it gives error saying php.ini file not found (something of this sort).

Can anybody please help me. Reply Delete

Thanks Hiren for this page, it’s helped me get SQL Server 2005 working with XAMPP.

I’m using:
Win XP w/ SP3
XAMPP 1.80
PHP Version 5.4.4
SQL Server 2005 w/SP4

For me, the Microsoft drivers listed in Hiren’s post above didn’t work.

; Drivers that work are here: http://downloads.php.net/pierre
;
; http://downloads.php.net/pierre/sqlsrv-5.4-ts-snap.zip
; sqlsrv-5.4-ts-snap.zip (2011-11-27 07:20 -0800)
; MD5 (sqlsrv-5.4-ts-snap.zip) = 3f885a897171d3a230ed16bf82fec5b9
;
extension=php_sqlsrv.dll
extension=php_pdo_sqlsrv.dl

Setup is working now with these drivers. Reply Delete

@Anonymous: i got this message
«This extension requires either the Microsoft SQL Server 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native Client ODBC Driver to communicate with SQL Server.» Reply Delete

Download SQL Server 2008 R2 Native Client from

hi. i need sql server connection with android eclipse. Kindly give me the code so that i can learn it
Reply Delete

Great my friend.
I find for many days for solutions on compatibility drivers to work with PHP 5.4, apache, SQLITE integrated with MSSQL or SQLSRV.
At this page was unique place where i found the solution, simple, clean, and exact. Very very thanks. Reply Delete

Hi I did the procedure give above but in my XAMPP i.e, localhost/info.php . I dont find the SQLSRV instead it shows no value now how to rectify the error am using MS SQLSERVER 2012 Reply Delete

Hi, thank you for this tutorial.

I’ve to add some things. If you are using PHP 5.4, the files are different.

You have to download the SQLSRV30.EXE, uncompress it and copy to the ext/ folder the following files:

Then, add the following lines to the php.ini in the extensions section:

hi i have just installed xampp and started the apache and its running but the mysql service is not running it doesnt start and this is what i see in the details:

Attempting to start MySQL service.
18:06:29 [mysql] Problem detected!
18:06:29 [mysql] Port 3306 in use by «»C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld» —defaults-file=»C:\Program Files\MySQL\MySQL Server 5.5\my.ini» MySQL»!
18:06:29 [mysql] MySQL WILL NOT start without the configured ports free!
18:06:29 [mysql] You need to uninstall/disable/reconfigure the blocking application
18:06:29 [mysql] or reconfigure MySQL and the Control Panel to listen on a different port

I followed all the steps and getting following error.

Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -49 [code] => -49 [2] => This extension requires the Microsoft SQL Server 2012 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712 [message] => This extension requires the Microsoft SQL Server 2012 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712 ) [1] => Array ( [0] => IM002 [SQLSTATE] => IM002 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ) )

Any help would be appreciated.

Источник

How to connect to SQL Server using PHP (Xampp) and PDO driver in Windows

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:

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

Driver Version	ODBC Driver or SQL Server Native Client Driver Version
5.8 or above	Microsoft ODBC 17.10.3 Driver for SQL Server ou Microsoft ODBC 18.2.1.1 Driver for SQL Server
4.3	Microsoft ODBC 11 Driver for SQL Server ou Microsoft ODBC 13.1 Driver for SQL Server
4.0	Microsoft ODBC 11 Driver for SQL Server ou Microsoft ODBC 13 Driver for SQL Server
3.2 or 3.1	Microsoft ODBC 11 Driver for SQL Server
3.0	SQL Server 2012 Feature Pack
2.0	Microsoft SQL Server 2008 R2 Native Client 32 bits (X86) ou Microsoft SQL Server 2008 R2 Native Client 64 bits (x64)

Relationship between driver version and database version

Driver Version	Database Version
4.3	SQL Server 2008 R2 and Above
4.0	SQL Server 2008 and above
3.1	SQL Server 2008 and above
2.0 and 3.0	SQL Server 2005 and above

Relationship between driver version and Windows version

Driver Version	Operating System Version
5.11	Windows Server 2012 Windows Server 2012 R2 Windows Server 2016 Windows 8 Windows 8.1 Windows 10 Windows 11

7 CentOS
Ubuntu 20.04, 20.10 & 21.04, 21.10
Debian 9, 10 & 11
Red Hat Enterprise Linux 7 & 8
SUSE 12 & 15

7 CentOS
Ubuntu 20.04, 20.10, 21.04 & 21.10
Debian 9, 10 & 11
Red Hat Enterprise Linux 7 & 8
SUSE 12 & 15

7 CentOS
Ubuntu 16.04, 20.04 & 20.10
Debian 9 & 10
Red Hat Enterprise Linux 7 & 8
SUSE 12 & 15

7 CentOS
Ubuntu 16.04 & 19.10
Debian 8 & 9 & 10
Red Hat Enterprise Linux 7 & 8
SUSE 12 & 15

After 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

Источник