Getting started with PHPVariablesArraysFunctional ProgrammingTypesAutoloading PrimerException Handling and Error ReportingWorking with Dates and TimeSending EmailSessionsCookiesClasses and ObjectsPassword Hashing FunctionsOutput BufferingJSONSOAP ClientReflectionUsing cURL in PHPDependency InjectionXMLRegular Expressions (regexp/PCRE)TraitsNamespacesParsing HTMLComposer Dependency ManagerMagic MethodsAlternative Syntax for Control StructuresFile handlingMagic ConstantsType hintingMulti Threading ExtensionFilters & Filter FunctionsGeneratorsOperatorsConstantsUTF-8URLsObject SerializationPHPDocContributing to the PHP ManualString ParsingLoopsControl StructuresSerializationClosureReading Request DataType juggling and Non-Strict Comparison IssuesSecurityPHP MySQLiCommand Line Interface (CLI)LocalizationDebuggingSuperglobal Variables PHPUnit TestingVariable ScopeReferencesCompilation of Errors and WarningsInstalling a PHP environment on WindowsDatetime ClassHeaders ManipulationPerformanceCommon ErrorsInstalling on Linux/Unix EnvironmentsContributing to the PHP CoreCoding ConventionsUsing MongoDBAsynchronous programmingUsing SQLSRVUnicode Support in PHPFunctionsCreate PDF files in PHPHow to Detect Client IP AddressYAML in PHPImage Processing with GDMultiprocessingSOAP ServerMachine learningCacheStreamsArray iterationCryptographyPDOSQLite3SocketsOutputting the Value of a VariableString formattingCompile PHP Extensionsmongo-phpManipulating an ArrayExecuting Upon an ArrayProcessing Multiple Arrays TogetherSPL data structuresCommentsIMAPUsing Redis with PHPImagickSimpleXMLHTTP AuthenticationRecipesBC Math (Binary Calculator)Docker deploymentWebSocketsAPCuDesign PatternsSecure Remeber Mephp mysqli affected rows returns 0 when it should return a positive integerPHP Built in serverHow to break down an URLPSR

Using SQLSRV

Other topics

Remarks:

The SQLSRV driver is a Microsoft supported PHP extension that allows you to access Microsoft SQL Server and SQL Azure databases. It is an alternative for the MSSQL drivers that were deprecated as of PHP 5.3, and have been removed from PHP 7.


The SQLSRV extension can be used on the following operating systems:

  • Windows Vista Service Pack 2 or later
  • Windows Server 2008 Service Pack 2 or later
  • Windows Server 2008 R2
  • Windows 7

The SQLSRV extension requires that the Microsoft SQL Server 2012 Native Client be installed on the same computer that is running PHP. If the Microsoft SQL Server 2012 Native Client is not already installed, click the appropriate link at the "Requirements" documentation page.


To download the latest SQLSRV drivers, go to the following: Download

A full list of system requirements for the SQLSRV Drivers can be found here: System Requirements

Those using SQLSRV 3.1+ must download the Microsoft ODBC Driver 11 for SQL Server

PHP7 users can download the latest drivers from GitHub

Microsoft® ODBC Driver 13 for SQL Server supports Microsoft SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016 (Preview), Analytics Platform System, Azure SQL Database and Azure SQL Data Warehouse.

Creating a Connection

$dbServer = "localhost,1234"; //Name of the server/instance, including optional port number (default is 1433)
$dbName = "db001"; //Name of the database
$dbUser = "user"; //Name of the user
$dbPassword = "password"; //DB Password of that user

$connectionInfo = array(
    "Database" => $dbName, 
    "UID" => $dbUser,
    "PWD" => $dbPassword
);

$conn = sqlsrv_connect($dbServer, $connectionInfo);

SQLSRV also has a PDO Driver. To connect using PDO:

$conn = new PDO("sqlsrv:Server=localhost,1234;Database=db001", $dbUser, $dbPassword);

Making a Simple Query

//Create Connection
$conn = sqlsrv_connect($dbServer, $connectionInfo);

$query = "SELECT * FROM [table]"; 
$stmt = sqlsrv_query($conn, $query);

Note: the use of square brackets [] is to escape the word table as it is a reserved word. These work in the same way as backticks ` do in MySQL.

Invoking a Stored Procedure

To call a stored procedure on the server:

$query = "{call [dbo].[myStoredProcedure](?,?,?)}"; //Parameters '?' includes OUT parameters

$params = array(
    array($name, SQLSRV_PARAM_IN),
    array($age, SQLSRV_PARAM_IN),
    array($count, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_INT) //$count must already be initialised
);

$result = sqlsrv_query($conn, $query, $params);

Making a Parameterised Query

$conn = sqlsrv_connect($dbServer, $connectionInfo);

$query = "SELECT * FROM [users] WHERE [name] = ? AND [password] = ?";
$params = array("joebloggs", "pa55w0rd");

$stmt = sqlsrv_query($conn, $query, $params);

If you plan on using the same query statement more than once, with different parameters, the same can be achieved with the sqlsrv_prepare() and sqlsrv_execute() functions, as shown below:

$cart = array(
    "apple" => 3,
    "banana" => 1,
    "chocolate" => 2
);

$query = "INSERT INTO [order_items]([item], [quantity]) VALUES(?,?)";
$params = array(&$item, &$qty); //Variables as parameters must be passed by reference

$stmt = sqlsrv_prepare($conn, $query, $params);

foreach($cart as $item => $qty){
    if(sqlsrv_execute($stmt) === FALSE) {
        die(print_r(sqlsrv_errors(), true));
    }
}

Fetching Query Results

There are 3 main ways to fetch results from a query:

sqlsrv_fetch_array()

sqlsrv_fetch_array() retrieves the next row as an array.

$stmt = sqlsrv_query($conn, $query);

while($row = sqlsrv_fetch_array($stmt)) {
    echo $row[0];
    $var = $row["name"];
    //...
}

sqlsrv_fetch_array() has an optional second parameter to fetch back different types of array: SQLSRV_FETCH_ASSOC, SQLSRV_FETCH_NUMERIC and SQLSRV_FETCH_BOTH(default) can be used; each returns the associative, numeric, or associative and numeric arrays, respectively.


sqlsrv_fetch_object()

sqlsrv_fetch_object() retrieves the next row as an object.

$stmt = sqlsrv_query($conn, $query);

while($obj = sqlsrv_fetch_object($stmt)) {
    echo $obj->field; // Object property names are the names of the fields from the query
    //...
}

sqlsrv_fetch()

sqlsrv_fetch() makes the next row available for reading.

$stmt = sqlsrv_query($conn, $query);

while(sqlsrv_fetch($stmt) === true) {
    $foo = sqlsrv_get_field($stmt, 0); //gets the first field -
}

Retrieving Error Messages

When a query goes wrong, it is important to fetch the error message(s) returned by the driver to identify the cause of the problem. The syntax is:

sqlsrv_errors([int $errorsOrWarnings]);

This returns an array with:

KeyDescription
SQLSTATEThe state that the SQL Server / OBDC Driver is in
codeThe SQL Server error code
messageThe description of the error

It is common to use the above function like so:

$brokenQuery = "SELECT BadColumnName FROM Table_1";
$stmt = sqlsrv_query($conn, $brokenQuery);

if ($stmt === false) {
    if (($errors = sqlsrv_errors()) != null) {
        foreach ($errors as $error) {
            echo "SQLSTATE: ".$error['SQLSTATE']."<br />";
            echo "code: ".$error['code']."<br />";
            echo "message: ".$error['message']."<br />";
        }
    }
}

Contributors

Topic Id: 4467

Example Ids: 15611,15612,15613,15614,15615,15616

This site is not affiliated with any of the contributors.