Connecting to Microsoft SQL Server in PHP on Linux

PHP and SQL Server work together wonderfully! Once you’ve gotten MS SQL Server installed on your Linux machine, it’s time to start running some real applications on it. Today we’ll look at querying SQL Server from PHP to enable your web applications.

We’re assuming you’ve already got MS SQL Server running on an Ubuntu machine. If you haven’t, take a look at our installation guide to get up to speed. We’ll be using Ubuntu, but the instructions aren’t that much different for RHEL or others.

Install PHP Extensions

Using PEAR, you’ll need to install and configure a few extensions for PHP to be able to access the MS SQL Server. PHP comes with the PHP Data Objects interface that provides a common way to access many different databases, but you need to install an extension for MS SQL Server to be able to use it PDO.

 

To install them, run the following PEAR command:

sudo pecl install sqlsrv pdo_sqlsrv

 

Then configure PHP to use the new extensions using bash (note the sudo!):

sudo echo "extension= pdo_sqlsrv.so" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`
sudo echo "extension= sqlsrv.so" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`

Connect to the Database

Now that PHP’s PDO knows how to interact with MS SQL Server, you can use it to connect to the server and run database commands. To establish a connection, use the global $sqlserv_connect function. For example, to connect to a local server:

<?php

//format: serverName\instanceName, portNumber (default is 1433)
$serverName = "localhost";

$connectionInfo = array( "Database"=>"dbName", "UID"=>"myUserName", "PWD"=>"myPassword");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Got a connection!<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

 

Run that on the command line and you should see “Got a connection!” indicating that you were successfully connected to the database. The hard part is over! All that’s left is running some commands on the connection.

Execute Queries

Let’s assume you’ve got a simple SQL table set up to track employees in your organization, with fields for employee name, id, and work site. You’d like to run INSERT and UPDATE queries to add employees and change their data as part of your web application. Once you’ve got the connection set up as above, you can execute any command you’d like. To insert new users:

$tsql= "INSERT INTO ExampleSchema.Employees (Name, Site) VALUES (?,?);";
$params = array('Beverly','Remote Office');
$getResults= sqlsrv_query($conn, $tsql, $params);
$numRows = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $numRows == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ( "inserted ".$numRows." row(s) " . PHP_EOL);

Notice how we use the $conn object we created above, and pass it to the global function sqlsrv_query. We also pass in a T-SQL statement and the associated parameters. Once the statement is executed, you also need to check to make sure you got a valid result.

Updates work the same way. We can change just the $tsql statement and get a working update:

$tsql= "UPDATE ExampleSchema.Employees SET Site= ? WHERE Name = ?);";
$params = array('Undersea Research Site', 'Beverly'); //etc.

Best Practices

Now that you’ve got a running application, be sure to check out the documentation on PDO. In particular, you’ll want to confirm after each statement that you execute that you got a correct result and that you got no errors. If you’re having trouble with your MS SQL Server on Linux, the “print_r( sqlsrv_errors(), true)” statement can be very helpful in debugging.

 

As the PHP driver is relatively young, you might also want to check out the GitHub page and the official documentation for new releases and for explanations of how to work around known issues.