<

PHP and Microsoft SQL Server

Published on
9,725 Points
6,225 Views
Last Modified:
Dave Baldwin
Zippity doo dah, zippity ay, my oh my what a wonderful day!!
Questions about connecting to Microsoft SQL Server using PHP come up fairly often.  This article covers a little of the basics and history.
History
It has almost always been easy to use PHP with MySQL.  They are both Open Source and have 'grown up' together.  In fact the MySQL people have always supplied the drivers for PHP.  And all-in-one programs like WAMP, XAMPP, and MAMP come with MySQL pre-configured to work.  http://php.net/manual/en/set.mysqlinfo.php

I don't know of any install that includes  Microsoft SQL Server that is pre-configured.   It has always been more difficult to use PHP with Microsoft SQL Server.  MS SQL was derived from SyBase many years ago and has always been a proprietary product. 

Drivers
Up until PHP 5.2, there was a 'mssql' driver http://php.net/manual/en/book.mssql.php which was written by the community with versions for both Linux and Windows.   'mssql' has been removed from PHP as of version 7.0.  With PHP 5.3, Microsoft started supplying the 'sqlsrv' driver http://php.net/manual/en/book.sqlsrv.php and wouldn't allow the 'mssql' driver to be used on Windows anymore.  The Linux 'mssql' driver requires FreeTDS and maybe UnixODBC.  The 'mssql' host is an entry in the FreeTDS table, not a direct connection to the database.  FreeTDS has not been updated for any SQL Server past SQL Server 2000 and does not handle some of the newer datatypes in Microsoft SQL Server.

While the MySQL drivers supplied with PHP were complete, the drivers for MS SQL have always required other programs and/or OS drivers to work.  The 'mssql' and 'sqlsrv' drivers have never been complete in themselves.  In Linux a program called FreeTDS has been required.  UnixODBC may also be required.  In Windows, one of the ODBC drivers has been required.  With SQL Server 2005, the SQL Native Client was required to access all of the features.  With the latest SQL Servers, the Microsoft ODBC Driver 11 for SQL Server is required.  System requirements for 'sqlsrv':  https://msdn.microsoft.com/en-us/library/cc296170%28sql.105%29.aspx

'mssql' driver does Not work with PHP 5.3 and later on Windows .  See here for 'sqlsrv' :  https://www.microsoft.com/en-us/download/details.aspx?id=20098   You will need version 3.2 to work with PHP 5.6.  And you will have to put the drivers in the 'ext' directory and add them to your 'php.ini'.  Please read the help file.  You will also need either the SQL Native Client or the Microsoft ODBC Driver 11, there are links under System Requirements on that page.

You will also probably need to rewrite some of the PHP code to use the 'sqlsrv' driver.  It is Not plug-in compatible with any other drivers.  The Help file SQLSRV_Help.chm has a lot of good code examples.

The 'sqlsrv' PHP driver for MS SQL is Not part of PHP and is Not updated when PHP is.  If someone updates your PHP version for you, it is likely to completely break your application.  Second, SQL statements for MS SQL are based on T-SQL which is Not exactly the same as the SQL used in MySQL.  https://msdn.microsoft.com/en-us/library/bb510741.aspx  In particular, the LIMIT statement used frequently in MySQL is not available in T-SQL.

Server Access
With MySQL, you have to create a user with external 'anyhost' privileges to connect to it from another computer and you may have to tell the server to allow external access.  With Microsoft SQL Server and the SQL Native Client or the Microsoft ODBC Driver 11, you have to enable TCP/IP access and on the server you have to enable external or remote access.  The default is Off.  And like MySQL, you have to create a user with the privileges you need.  A lot of people use the 'sa' account but that is like using 'root' in MySQL and not recommended.  The privileges are too high and allow access to almost everything on the server.  A more limited user is recommended that has only the privileges necessary to do their work.  Here is a screen shot of the SQL Server related configuration items in the Computer Management part of the Administrative Tools on Windows 7.  I have the same 3 protocols enabled for the client and the server.
SQL-Protocols.jpgUtility Programs
MySQL has MySQL Workbench and Microsoft SQL Server has SQL Server Management Studio (Express).  In addition, MySQL has phpMyAdmin https://www.phpmyadmin.net/ which will run on the web server.  And there is adminer4  https://www.adminer.org/ (formerly phpMinAdmin) which will connect to MySQL, MS SQL, PostgreSQL, SQLite, Oracle, Firebird, SimpleDB, Elasticsearch and MongoDB (if you have the PHP drivers for them).

Miscellaneous
This page https://msdn.microsoft.com/en-us/library/ms810810.aspx  shows an overview of "Microsoft's data access technologies."

This page covers PHP ODBC:  http://php.net/manual/en/intro.uodbc.php  It turns out that PHP ODBC requires the same MS SQL drivers as the MS PHP driver called 'sqlsrv'.  The 'sqlsrv' driver functions are more current than the PHP ODBC functions.
 
0
Comment
0 Comments

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month