Connect to MS SQL server in PHP7 on Ubuntu Linux 16.04 LTS

Working with SQL Server in php on Linux can be a pain. I use FreeTDS to solve this issue.

PHP7 odbc

To install this module run the following command in the terminal.

apt-get install php7.0-odbc  


To install and configure FreeTDS run the following commands in the terminal.

apt-get install freetds-bin freetds-common tdsodbc odbcinst unixodbc unixodbc-dev  
mv /etc/odbcinst.ini /etc/odbcinst.ini.bak  
cp /usr/share/tdsodbc/odbcinst.ini /etc/  

Test the connection in the terminal

You can test the connection by using 'tsql'. When you see an error or some counter there is something wrong. If things go well you get an sql prompt where you can run queries. Don't forget to type 'go' and [enter] to execute the query. Type 'exit' and [enter] to close the prompt.

tsql -H my_server_host -p 1433 -U my_user -P my_password -D my_database  

Test everything in PHP7

If the terminal test is OK then you can use this code to test the connection in php.

try {

    //connection params
    $dbCon = new PDO('odbc:Driver=FreeTDS; Server=my_server_host; Port=1433; Database=my_database; TDS_Version=7.4; ClientCharset=UTF-8', 'my_login', 'my_pass');

    //test query
    $result = $dbCon->query('SELECT TOP 10 * FROM dbo.my_table');

    //show the results
    foreach ($result as $row)
        print_r( $row );

    //close the connection
    $dbCon = null;

} catch (PDOException $e) {

    //show exception
    echo $e->getMessage();


David Van De Walle

Eager to learn, develop and disrupt !


comments powered by Disqus