Linux based PHP install connecting to MsSQL Server
What is the best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server. The PHP will only ever run on a Linux box. I’ve been trawling for the simplest answer for a while now.
6 Answers 6
Php 5.6
sudo apt-get install php5.6-sybase freetds-common libsybdb5 AWS / Centos / Redhat
sudo yum install php56-mssql After that, you can connect to the MsSql database through PHP with something like this:
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch (PDOException $e) < echo "There was a problem connecting. " . $e->getMessage(); > $query = "SELECT * FROM TestSchema.Employees"; $statement = $pdo->prepare($query); $statement->execute(); $results = $statement->fetchAll(PDO::FETCH_ASSOC); var_dump($results); You can troubleshoot MsSQL with something like this on the command line:
tsql -H your.server.name -p 1433 -U yourusername -P yourpassword -D yourdatabasename To install tsql you can run this:
Installation of SQL binaries for testing on any PHP version
sudo apt install freetds-bin Php 7+
Microsoft has native drivers we can use. Full instructions are here (Redhat / Ubuntu / Others).
That link also contains required steps to install Ms SQL server on your dev machine (working on Ubuntu, doesn’t work on AWS, but you can just spin up an RDS instance there). It also contains basic instructions on how to create test tables and data in the database, and PHP connectivity code.
You can also install the components for a newer version of PHP like this:
sudo apt-get install php7.2-sybase freetds-common libsybdb5 The above did not work for me in docker with PHP 5.6 but this did the trick -> github.com/docksal/docksal/issues/473#issuecomment-374855050
you must to install mssql driver for php on linux.
this is a best tutorial for you.
sudo apt-get install php5-sybase php5-odbc freetds-common Edit freetds.conf then connect MSSQL server with this PHP.
After searching and trying out the many suggestions here and in other posts, and spending a lot of time, a colleague suggested trying ADO.
As we already had an ADO enabled PHP install, it literally took less than 10 minutes to get up and running.
If your serious about connecting from Linux PHP to MS-SQL, consider ADO.
My apologies if I don’t understand the protocal here, but I’m pretty sure I gave pretty good answer. The question was: ‘What is the best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server.’ My answer is: The best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server is to use the PHP ADO library. There are many examples. Here’s the first one I found: stackoverflow.com/questions/10881599/php-adodb-mssql-connection
It’s interesting to note that every answer here proposes one solution to the problem rather than answering the question asked which was which is the «best» solution. Unfortunately the OP forgot to tell us what the criteria were for «best».
Nobody so far has mentioned odbc. While this entails both configuring the odbc driver for connecting to the database and enabling the php extension, this method provides the best isolation of the php code from the underlying database making it easier to port the code later.
The microsoft provided drivers for php should give good compatibility but only support v7 up of php.
You may prefer to stick with the functionality provided by your Linux distro to ensure you have a supportable product and/or automated patch management. An alternative to odbc here may be the freetds driver but you didn’t tell us anything about which Linux distro this is.
Microsoft SQL Server Functions (PDO_SQLSRV)
PDO_SQLSRV is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MS SQL Server (starting with SQL Server 2005) and SQL Azure databases.
Installation
The most recent version of the driver is available for download here: » SQLSRV download. The driver sources are hosted in a » public repository.
For more information about system requirements, see » SQLSRV System Requirements.
On Windows the PDO_SQLSRV extension is enabled by downloading and adding appropriate DLL files to your PHP extension directory and the corresponding entry to the php.ini file.
On Linux and macOS, the PDO_SQLSRV extension can be installed using » PECL. See the » installation tutorial for details.
Predefined Constants
The constants below are defined by this driver, and will only be available when the extension has been either compiled into PHP or dynamically loaded at runtime. In addition, these driver-specific constants should only be used if you are using this driver. Using driver-specific attributes with another driver may result in unexpected behaviour. PDO::getAttribute() may be used to obtain the PDO::ATTR_DRIVER_NAME attribute to check the driver, if your code can run against multiple drivers.
PDO::SQLSRV_TXN_READ_UNCOMMITTED ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Read Uncommitted. PDO::SQLSRV_TXN_READ_COMMITTED ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Read Committed. PDO::SQLSRV_TXN_REPEATABLE_READ ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Repeateable Read. PDO::SQLSRV_TXN_SNAPSHOT ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Snapshot. PDO::SQLSRV_TXN_SERIALIZABLE ( int ) This constant is an acceptable value for the SQLSRV DSN key TransactionIsolation. This constant sets the transaction isolation level for the connection to Serializable. PDO::SQLSRV_ENCODING_BINARY ( int ) Specifies that data is sent/retrieved as a raw byte stream to/from the server without performing encoding or translation. This constant can be passed to PDOStatement::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam. PDO::SQLSRV_ENCODING_SYSTEM ( int ) Specifies that data is sent/retrieved to/from the server as 8-bit characters as specified in the code page of the Windows locale that is set on the system. Any multi-byte characters or characters that do not map into this code page are substituted with a single byte question mark (?) character. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam. PDO::SQLSRV_ENCODING_UTF8 ( int ) Specifies that data is sent/retrieved to/from the server in UTF-8 encoding. This is the default encoding. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam. PDO::SQLSRV_ENCODING_DEFAULT ( int ) Specifies that data is sent/retrieved to/from the server according to PDO::SQLSRV_ENCODING_SYSTEM if specified during connection. The connection’s encoding is used if specified in a prepare statement. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, PDO::prepare, PDOStatement::bindColumn, and PDOStatement::bindParam. PDO::SQLSRV_ATTR_QUERY_TIMEOUT ( int ) A non-negative integer representing the timeout period, in seconds. Zero (0) is the default and means no timeout. This constant can be passed to PDOStatement::setAttribute, PDO::setAttribute, and PDO::prepare. PDO::SQLSRV_ATTR_DIRECT_QUERY ( int ) Indicates that a query should be executed directly, without being prepared. This constant can be passed to PDO::setAttribute, and PDO::prepare. For more information, see » Direct and Prepared Statement Execution.
Table of Contents
Загрузка драйверов Майкрософт для PHP для SQL Server
Драйвер SQL Server для PHP обеспечивает надежную и масштабируемую интеграцию приложений PHP с сервером SQL Server. Эти драйверы представляют собой расширения для PHP, которые позволяют считывать и записывать данные SQL Server из скриптов PHP. Драйверы предоставляют интерфейсы для доступа к данным в Базе данных SQL Azure и во всех выпусках SQL Server 2012 и более поздних версий (включая экспресс-выпуски). Драйвер использует функции PHP (в том числе потоки) для чтения и записи крупных объектов.
В Linux и macOS драйверы для PHP легко скачать и установить с помощью PECL. Подробнее см. учебник по установке для Linux и Mac. Если необходимо скачать и установить драйверы для PHP в Linux и macOS вручную, пакеты для этих платформ можно найти в тегах выпуска GitHub.
Скачивание
Драйверы Майкрософт версии 5.11 для PHP для SQL Server — это последняя общедоступная (GA) версия.
Убедитесь, что у вас установлена последняя версия драйвера ODBC, чтобы обеспечить оптимальную производительность и безопасность. Сведения о скачивании см. в разделе Скачивание драйвера ODBC для SQL Server.
Сведения о версии
Если вы хотите оставить отзыв, лучше всего обратиться к команде Microsoft Drivers Майкрософт для PHP для SQL Server, создав проблему в репозитории GitHub.
Заметки о выпуске
См. сведения о новых возможностях в этом выпуске в заметках о выпуске SSMS.
Предыдущие выпуски
Эта страница описывает возможности только последней версии Microsoft Drivers для PHP. Чтобы скачать предыдущие версии, см. страницу с предыдущими выпусками Microsoft Drivers для PHP для SQL Server.