Microsoft SQL Server in PHP

The Microsoft SQL engine comes in two flavors: the Microsoft SQL Server Developer and the Microsoft SQL Server Express, available for free at https://microsoft.com/sql-server/sql-server-downloads. Both versions are supported by PHP because they are compatible with each other.

In this tutorial, we use the sqlsrv extension, which is only available for Windows operating systems developed and maintained by Microsoft. Use PDO extension for other operating system.

Connecting to SQL Server

We connect to the server using sqlsrv_connect() and select the database to be used using an associative array (you can find the complete list of connection options at https://learn.microsoft.com/sql/connect/php/connection-options):

<?php
 $server = 'ServerName\\InstanceName';
 $options= array(
     'Database'=>'db_name',
     'UID'=>'username', 
     'PWD'=>'password');

 $conn = sqlsrv_connect( $server, $options);
 
 if (! $conn) {
  $errors = sqlsrv_errors();
  foreach( $errors as $e ) {
   echo $e['SQLSTATE'] .', '.
        $e['code'] .', '.
        $e['message'];
  }
  exit;
 }

 echo 'Connection established';
 sqlsrv_close();

The sqlsrv_errors() function returns an array of arrays containing the error and warning information about the last SQLSRV operation performed.

Sending SQL Query

The function sqlsrv_query() function prepares and executes an SQL query. The first parameter accepts the database handle, the second one accepts an SQL statement, and the third one accepts an array specifying parameter information when executing a parameterized query.

<?php
 //...
 $conn = sqlsrv_connect( $server, $options);
 
 $sql = "INSERT INTO user_table (name, email) VALUES (?, ?)";
 $params = array($_POST['name'], $_POST['email']);
 
 $stmt = sqlsrv_query( $conn, $sql, $params);
 if( $stmt === false ) {
  echo '<pre>';
  print_r(sqlsrv_errors());
  exit;
 }
 
 echo 'Record added';

Retrieving Results of a Query to MSSQL

The following functions are used to retrieve all data in the resultset:

sqlsrv_fetch_array() returns the current row in the resultset as a numeric or associative array.
sqlsrv_fetch_object() returns the current row in the resultset as an object
sqlsrv_fetch() makes the next row in a result set available for reading (use sqlsrv_get_field() to read the fields of the row).

<?php
 //...
 $conn = sqlsrv_connect( $server, $options);
 $sql = 'SELECT * FROM user_table LIMIT 5';

 $stmt = sqlsrv_query( $conn, $sql);
 
 while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
  echo $row['name'].', '.$row['email'].'<br>';
 }

 /* //Retrieving a numeric array 
 while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC) ) {
  echo $row[0].', '.$row[0].'<br>';
 } */

Working with Databases: