Working with SQL server in PHP projects

Now and then, there is a client which uses SQL server and set it as a requirement. By default, PHP can work with databases like MySQL, SQLite or PostgreSQL. In the case for SQL Server, you can do it but it requires some manual. If you work with Windows, you can set it up very easily after a couple of wizards. But if you work with OSX or any Linux distro, you will not have a good time. Docker to the rescue!.

I use docker to install the drivers and the oficial MS SQL Server docker image. Checkout the final result here

Original Source: https://daniel.noyola.dev/sqlsrv-php/

Dockerfile

I’m going to use the official PHP docker image

command

Below FROM php:fpm we’re going to add several things. First, you need to install all the dependencies

RUN apt-get update && apt-get install -y locales unixodbc libgss3 odbcinst \     devscripts debhelper dh-exec dh-autoreconf libreadline-dev libltdl-dev \     tdsodbc unixodbc-dev wget unzip apt-transport-https \     libfreetype6-dev libmcrypt-dev libjpeg-dev libpng-dev \     && rm -rf /var/lib/apt/lists/* \     && docker-php-ext-install pdo \     && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen

From those dependencies, the most important is unixODBC. ODBC is an open specification for providing application developers with a predictable API with which to access Data Sources like SQL server and unixODBC is a definition for non MS Windows platforms.

After that we need to add and install the Microsoft ODBC drivers for Linux:

RUN apt-get update \

Then we just install and enable the PHP extensions.

RUN pecl install WINCACHE-1.3.7.12 pdo_sqlsrv-5.6.1 sqlsrv-5.6.1 \

And finally, we run the SQL Server process

CMD /opt/mssql/bin/sqlservr

This would be the final dockerfile

FROM php:fpm

Adding SQL Server

For this, we will use the official docker image … it’s not like we have another option. You can read the whole documentacion here. Here’s the docker-compose file.

version: '3'

One importart thing here. Don’t forget to add the volumes. If you don’t, you will lost all data.

Testing

Create a index.php in the root directory and add a phpinfo() to make sure that it is working and the PHP has the pdo_sqlsrv and sqlsrv extensions enable. If you want to see every thing working, checkout this repo: https://github.com/danielnv18/php-sqlsrv-example

 

Original Source: https://daniel.noyola.dev/sqlsrv-php/