Accessing SQL Server Databases with PHP
SQL Server Technical Article
This white paper is now maintained in the TechNet Wiki so that the Community can provide comments and improvements. For the most current and updated version of this article, please see https://social.technet.microsoft.com/wiki/contents/articles/whitepaper-accessing-sql-server-databases-from-php.aspx .
Writer: Brian Swan
Published: August 2008
Applies to: SQL Server 2005 and SQL Server 2008 – all editions
Summary: The SQL Server 2005 Driver for PHP is a Microsoft-supported extension of PHP 5 that provides data access to SQL Server 2005 and SQL Server 2008. The extension provides a procedural interface for accessing data in all editions of SQL Server 2005 and SQL Server 2008. The SQL Server 2005 Driver for PHP API provides a comprehensive data access solution from PHP, and includes support for many features including Windows Authentication, transactions, parameter binding, streaming, metadata access, connection pooling, and error handling. This paper discusses how to use several of these features by closely examining parts of the Example Application in the SQL Server 2005 Driver for PHP product documentation in MSDN. This paper assumes that the reader is familiar with programming in PHP, that the reader has a computer that meets the System Requirements listed for using the driver, and that the AdventureWorks example database is installed from CodePlex.
Introduction
The Microsoft SQL Server 2005 Driver for PHP allows PHP developers to access data in SQL Server 2005 and SQL Server 2008 databases. The driver includes support for Windows and SQL Server Authentication methods, transactions, parameter binding, streaming, metadata access, connection pooling, and error handling.
This paper explains how to load and configure the driver, and discusses how to leverage several of the features mentioned above. Parts of the Example Application in the product documentation will be used to demonstrate these programming scenarios.
For more information about a complete list of driver features and functions, see the API Reference in the SQL Server 2005 Driver for PHP product documentation in MSDN. The prefix for all driver functions is sqlsrv.
The SQL Server Driver for PHP relies on the Microsoft SQL Server 2005 ODBC Driver to handle the low-level communication with SQL Server. As a result, the SQL Server Driver for PHP is only supported on Windows. Microsoft provides support for this driver under its normal support methods. While the source code for this driver has been made available on the codeplex.com website, Microsoft supports only the signed version of the driver from the MSDN download site.
This paper assumes that the reader is familiar with programming in PHP, that the reader has a computer that meets the System Requirements listed for using the driver, and that the AdventureWorks example database is installed.
Loading the Driver
You can download the SQL Server 2005 Driver for PHP at the Microsoft Download Center. Included in the download are two .dll files: php_sqlsrv.dll and php_sqlsrv_ts.dll. These files correspond to the non-thread-safe and thread-safe versions of the driver respectively.
Loading the SQL Server 2005 Driver for PHP is similar to loading any PHP extension:
1. Put the extension file (php_sqlsrv.dll or php_sqlsrv_ts.dll) in the PHP extension directory. If you are running the non-thread-safe version of PHP (php5.dll), you should use the non-thread-safe version of the driver (php_sqlsrv.dll). Similarly, if you are running the thread-safe version of PHP (php5ts.dll), you should use the thread-safe version of the driver (php_sqlsrv_ts.dll).
2. Modify the php.ini file to include the extension.
Depending on which version of the driver you want to load (non-thread-safe or thread-safe), you will need to add one of the following lines to the Dynamic Extensions section of your php.ini file:
extension=php_sqlsrv.dll
-or-
extension=php_sqlsrv_ts.dll
See Figure 1 below for more detail.
3. Restart the Web server.
For more information, see Loading the Driver in the product documentation.
Configuring the Driver
The SQL Server 2005 Driver for PHP has three configuration options:
· LogSubsystems
Use this option to turn the logging of subsystems on or off. The default setting is SQLSRV_LOG_SYSTEM_OFF (logging is turned off by default).
· LogSeverity
Use this option to specify what to log after logging has been turned on. The default setting is SQLSRV_LOG_SEVERITY_ERROR (only errors are logged by default after logging has been turned on).
· WarningsReturnAsErrors
By default, the SQL Server 2005 Driver for PHP treats warnings generated by sqlsrv functions as errors. Use the WarningsReturnAsErrors option to change this behavior. The default setting for this option is true (1).
Note There are exceptions to this rule. For example, the warning generated by changing the database context is never treated as an error.
For more information about these options and settings, see Configuring the Driver in the product documentation.
Settings for the configuration options can be set in the php.ini file, or they can be set in a PHP script with the sqlsrv_configure function. The figure below shows the Dynamic Extensions section of the php.ini file modified to load the thread-safe version of the driver, log activity on all subsystems, log all activity (errors, warnings, and notices), and turn off the WarningsReturnAsErrors behavior.
.jpg "Description: Description: Description: Description: Description: Figure1.jpg")
Figure 1: The Dynamic Extensions section of the php.ini.
For more information about how to change the default settings, see Logging Activity and How to: Configure Error and Warning Handling in the product documentation.
One way to be sure that the driver is loaded and to see the configuration settings is to run a script that calls the phpinfo() function. To do this, follow these steps:
1. Open a text file and copy the following code into it:
<?php phpinfo(); ?>
2. Save the file as info.php in your Web server’s root directory.
3. Open a browser and go to https://localhost/info.php.
4. Scroll down the resulting page to find the sqlsrv section.
The following figure shows the sqlsrv section of the phpinfo() page. This output confirms the driver is loaded and the configuration settings are set to default values.
.jpg "Description: Description: Description: Description: Description: Figure 2.jpg")
Figure 2: The sqlsrv section of the phpinfo() page.
Creating a Connection
The sqlsrv_connect function is used to establish a connection to the server. The code shown here (from the Example Application in the product documentation) establishes a connection to the local server and specifies the AdventureWorks database as the database in use:
$serverName = "(local)";
$connectionOptions = array("Database"=>"AdventureWorks");
/* Connect using Windows Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionOptions);
if( $conn === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
By default, the sqlsrv_connect function uses Windows Authentication to establish a connection. In most scenarios, this means that the Web server's process identity or thread identity (if the Web server is using impersonation) is used to connect to the server, not an end-user's identity.
The sqlsrv_connect function accepts two parameters: $serverName and $connectionOptions (optional).
· $serverName – This required parameter is used to specify the name of the server to which you want to connect. In the code above, a connection is established to the local server. This parameter can also be use to specify a SQL Server instance or a port number. For example:
$serverName = "myServer\instanceName";
-or-
$serverName = "myServer, 1521";
· $connectionOptions - This optional parameter is an array of key-value pairs that set options on the connection. In the code above, the database is set to AdventureWorks for the connection. Other options include ConnectionPooling, Encrypt, UID, and PWD. For more information, see sqlsrv_connect in the product documentation.
Note The UID and PWD options must be set in the $connectionOptions parameter to log into the server with SQL Server Authentication.
For more information about creating a connection, see Connecting to the Server in the product documentation.
Note The FormatErrors function that is shown in the example is a custom function for formatting error output. It is described in the Handling Errors and Warnings section later in this paper.
Connection Pooling
The SQL Server 2005 Driver for PHP is designed to use ODBC connection pooling. By default, connection pooling is enabled. When you connect to a server, the driver attempts to use a pooled connection before it creates a new one. If an equivalent connection is not found in the pool, a new connection is created and added to the pool. The driver determines whether connections are equivalent based on a comparison of connection strings. Calling sqlsrv_close on a connection returns the connection to the pool. However, if the connection was created with the ConnectionPooling attribute set to false (see the Creating a Connection section), calling sqlsrv_close closes the connection.
Note* *The first time you execute a query on a connection that was retrieved from a pool, the driver tells the server to reset the connection prior to executing the query. Resetting the connection returns the connection to its original state. For example, resetting the connection deletes any temporary objects and rolls back any pending transactions.
For more information, see Connection Pooling in the product documentation.
Executing a Query
The SQL Server 2005 Driver for PHP provides two options for executing queries: the sqlsrv_query function, or the combination of the sqlsrv_prepare and sqlsrv_execute functions.
The sqlsrv_query function does both statement preparation and execution with one function call and is best suited for executing one-time queries. An alternate method for executing queries (a method well-suited for executing a query multiple times with different parameter values) is the combination sqlsrv_prepare and sqlsrv_execute. This option breaks statement preparation and execution into two function calls. For more information, see Comparing Execution Functions in the product documentation.
The general programming pattern for either option requires doing the following before calling sqlsrv_query or sqlsrv_prepare/sqlsrv_execute:
· Establishing a connection to the server (see the Creating a Connection section)
· Defining a Transact-SQL statement
· Providing an array of parameter values (only required for parameterized queries)
· Setting options on the connection (optional)
The following code (from the Example Application in the product documentation) demonstrates the use of the sqlsrv_query function:
$tsql = "SELECT ProductID, Name, Color, Size, ListPrice
FROM Production.Product
WHERE Name LIKE '%' + ? + '%' AND ListPrice > 0.0";
$params = array( $_REQUEST['query'] );
$getProducts = sqlsrv_query( $conn, $tsql, $params);
if ( $getProducts === false)
{ die( FormatErrors( sqlsrv_errors() ) ); }
The sqlsrv_query and sqlsrv_prepare functions each accept four parameters: $conn, $tsql, $params (optional), and $options (optional, not shown).
· $conn – This required parameter is a PHP connection resource created with the sqlsrv_connect function (see the Creating a Connection section).
· $tsql – This required parameter is a string that defines a Transact-SQL query. Question marks (?) are used as placeholders for parameters.
· $params – This optional parameter is an array of values that correspond (in order) to the parameter placeholders (question marks) in the query defined by the $tsql parameter. Each value in the $params array can be a literal value (such as 5), a PHP variable (such as $myVar), or an array with the following structure:
array($value [, $direction [, $phpType [, $sqlType]]])
This array is used to specify the parameter value, the parameter direction (in the case where the parameter is being passed to a stored procedure), the PHP type of the parameter, and the SQL Server type of a value sent to the server. For more information about this array, see the Sending Images to the Server section. For more information, see Using Directional Parameters, How to: Send Data as a Stream, and How to: Specify SQL Server Data Types in the product documentation.
· $options – This optional parameter (not shown in the example) is an associative array that sets properties on the query. Two keys are supported: QueryTimeout and SendStreamParamsAtExec. The QueryTimeout key sets the maximum time in seconds that a query is allowed to run. The SendStreamParamsAtExec key determines if all stream data is sent at the time of query execution or if subsequent calls to sqlsrv_send_stream_data are necessary to send all stream data. For more information, see How to: Send Data as a Stream.
Retrieving Data from the Server
The general pattern for retrieving data with the SQL Server 2005 Driver for PHP involves defining and executing a query (see the Executing a Query section) and then using one of the following three options to retrieve data from the result set.
· The sqlsrv_fetch_array function (retrieves a row of data as an array).
· The sqlsrv_fetch_object function (retrieves a row of data as a PHP object).
· The combination of the sqlsrv_fetch and sqlsrv_get_field functions (retrieves a single field from a row of data).
Note* *These functions provide forward-only access to the rows of a result set.
When choosing which option to use, consider the following:
· The sqlsrv_fetch_array and sqlsrv_fetch_object functions pull an entire row of data into script memory. This may not be desirable for rows that contain large amounts of data.
· Data returned by the sqlsrv_fetch_array and sqlsrv_fetch_object functionswill be typed according to the defaults PHP data types assigned by the driver. For more information, see Default PHP Data Types in the product documentation.
· Using the combination of sqlsrv_fetch and sqlsrv_get_field allows you to specify the PHP data type of the returned data, including specification of the data as a stream.
For more information about data retrieval functionality, see Comparing Data Retrieval Functions in the product documentation.
For more information about retrieving data with the SQL Server 2005 Driver for PHP see Retrieving Data in the product documentation.
Retrieving Data as an Array
In this section, we examine code in the Example Application that retrieves data as an array. The following code uses the sqlsrv_fetch_array function to retrieve one row at a time from a result set. Each row is retrieved as an associative array and is passed to the custom function PopulateProductsTable for processing:
$productCount = 0;
while( $row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC))
{
PopulateProductsTable( $row );
$productCount++;
}
The sqlsrv_fetch_array function accepts two parameters, $stmt and $fetchType (optional):
· The $stmt parameter is a PHP statement resource created with sqlsrv_query or sqlsrv_execute (see the Executing a Query section).
· The $fetchType parameter (optional) is a driver-defined constant that specifies what type of array will be returned: associative, numeric, or both. The corresponding constants are SQLSRV_FETCH_ASSOC, SQLSRV_FETCH_NUMERIC, and SQLSRV_FETCH_BOTH. By default, an array with both types of indices is returned.
Retrieving Images
In this section, we examine code in the Example Application that retrieves an image from the server. The code below executes a query that retrieves an image from the server, specifies that the returned data be retrieved as a binary stream, and dumps the data to the Web page using the PHP fpassthru function:
/* Get the product picture for a given product ID. */
$tsql = "SELECT LargePhoto
FROM Production.ProductPhoto AS p
JOIN Production.ProductProductPhoto AS q
ON p.ProductPhotoID = q.ProductPhotoID
WHERE ProductID = ?";
$params = array($_REQUEST['productId']);
/* Execute the query. */
$stmt = sqlsrv_query($conn, $tsql, $params);
if( $stmt === false )
{
echo "Error in statement execution.</br>";
die( print_r( sqlsrv_errors(), true));
}
/* Retrieve the image as a binary stream. */
$fieldIndex = 0;
$getAsType = SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY);
if ( sqlsrv_fetch( $stmt ) )
{
$image = sqlsrv_get_field( $stmt, $fieldIndex, $getAsType);
fpassthru($image);
}
else
{
echo "Error in retrieving data.</br>";
die(print_r( sqlsrv_errors(), true));
}
The code above defines a parameterized Transact-SQL query ($tsql), specifies the parameter value ($params), and executes the query with the sqlsrv_query function (see the Executing a Query section). The result set is consumed by calling sqlsrv_fetch (to make the next row of the result set available for reading) followed by calling sqlsrv_get_field (to read one field in the active row). The sqlsrv_fetch function takes a single parameter ($stmt in the example) that is a PHP resource created by sqlsrv_query or sqlsrv_execute. The sqlsrv_get_field takes three parameters, $stmt, $fieldIndex, and $getAsType (optional):
· The $stmt parameter is a PHP resource corresponding to an executed statement. The next (or first) row of data is made available to the sqlsrv_get_field function by first passing the $stmt parameter to the sqlsrv_fetch function.
· The $fieldIndex specifies the index of the field to be retrieved. Indices start at zero.
· The $getAsType parameter (optional) is used to specify the PHP type (and encoding, in this case) of the returned data. If this parameter is not provided, data will be returned according to its default PHP type. For more information, see Default PHP Data Types in the product documentation.
For more information about retrieving images and binary/large data, see Retrieving Data as a Stream in the product documentation.
Retrieving data with the combination of the sqlsrv_fetch and sqlsrv_get_field functions can be used to specify the PHP type of returned data, not only for retrieving data as a stream. For more information, see How to: Specify PHP Data Types in the product documentation.
Sending Data to the Server
The general pattern for sending data to the server involves executing an appropriate Transact-SQL query (such as an UPDATE or INSERT query) with the sqlsrv_query function or the combination of the sqlsrv_prepare and sqlsrv_execute functions (see the Executing a Query section). For example, the code below (from the Example Application in the product documentation) sends a product review to the server using the combination of the sqlsrv_prepare and sqlsrv_execute functions:
/*Prepend the review so it can be opened as a stream.*/
$comments = "data://text/plain,".$_REQUEST['comments'];
$stream = fopen( $comments, "r" );
$tsql = "INSERT INTO Production.ProductReview (ProductID,
ReviewerName,
ReviewDate,
EmailAddress,
Rating,
Comments)
VALUES (?,?,?,?,?,?)";
$params = array($_REQUEST['productid'],
$_REQUEST['name'],
date("Y-m-d"),
$_REQUEST['email'],
$_REQUEST['rating'],
$stream);
/* Prepare and execute the statement. */
$insertReview = sqlsrv_prepare($conn, $tsql, $params);
if( $insertReview === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
/* By default, all stream data is sent at the time of query execution. */
if( sqlsrv_execute($insertReview) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
The parameters for sqlsrv_prepare are the same as those for sqlsrv_query (see the Executing a Query section). The sqlsrv_execute function takes one parameter ($insertReview in the example) that is a PHP resource specifying the prepared statement to be executed.
Note The query in the example could have been executed with the sqlsrv_query function. The recommended practice for executing a single query is to use the sqlsrv_query function. The use of sqlsrv_prepare and sqlsrv_execute in the example is done to demonstrate how these functions are used together.)
This example highlights the driver’s streaming capabilities. The customer’s comments ($comments) are opened as a text stream ($stream), which is a parameter in the query. By default, all stream data is sent to the server at the time of query execution. However, the driver also provides functionality that allows up to 8KB of stream data to be sent to the server at a time.For more information, see the Sending Images to the Server section below or How to: Send Data as a Stream in the product documentation.
For more information about sending data to the server with the SQL Server 2005 Driver for PHP, see Updating Data in the product documentation.
Sending Images to the Server
In this section, we examine code in the Example Application that sends an image to the server as a binary stream. The following code opens an image as a stream and then sends the file up to the server in parts up to 8KB at a time:
$tsql = "INSERT INTO Production.ProductPhoto (LargePhoto)
VALUES (?);
SELECT SCOPE_IDENTITY() AS PhotoID";
$fileStream = fopen($_FILES['file']['tmp_name'], "r");
$params = array(
array(
$fileStream,
SQLSRV_PARAM_IN,
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
SQLSRV_SQLTYPE_VARBINARY('max')
)
);
/* Turn off the default behavior of sending all stream data
to the server at the time of query execution. */
$options = array("SendStreamParamsAtExec"=>0);
$uploadPic = sqlsrv_prepare($conn, $tsql, $params, $options);
if( $uploadPic === false )
{ die( FormatErrors( sqlsrv_errors() ) );}
if( sqlsrv_execute($uploadPic) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
/* Stream data to the database in chunks. */
while( $success = sqlsrv_send_stream_data( $uploadPic))
{
}
As noted in the previous example, the general pattern for sending data to the server involves executing an appropriate Transact-SQL query (such as an UPDATE or INSERT statement). The notable differences in this example are described here:
· Types are specified in the in the $params array. The PHP type must be specified here so that the driver will interpret the data as a binary stream. The SQL Server type must be specified so that the server will interpret the incoming data correctly.
For more information, see How to: Specify PHP Data Types and How to: Specify SQL Server Data Types in the product documentation.
· The $options parameter is used when calling sqlsrv_prepare. The default behavior of sending all stream data to the server at the time of query execution is turned off by setting "SendStreamParamsAtExec" to 0 in the $options parameter. When this behavior is turned off, calls to sqlsrv_send_stream data (after query execution) are required to send stream data to the server (see next bullet).
· The sqlsrv_send_stream data function is used to send data up to 8KB of stream data to the server at a time. Turning off the default behavior of sending all stream data to the server at once and using sqlsrv_send_stream_data to send stream data allows for flexibility in application design. For example, it allows an application to present users with a progress bar when uploading a large image.
Moving Through Groups of Result Sets
The SQL Server 2005 Driver for PHP provides the sqlsrv_next_result function for moving forward through multiple results returned by batch queries or stored procedures. This function makes the next result set, row count, or output parameter of an active statement available for reading.
The code below demonstrates how to use sqlsrv_next_result to move through results. The code here is taken from the Example Application in the product documentation and immediately follows the code in the previous example. The $uploadPic statement corresponds to a batch query in the previous example. The code here moves to the second result in that statement and uses the retrieved value to execute a query that associates the new ProductPhotoID with a ProductID:
/*Skip the open (first) result set (rows affected). */
$next_result = sqlsrv_next_result($uploadPic);
if( $next_result === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
/* Fetch the next result set. */
if( sqlsrv_fetch($uploadPic) === false)
{ die( FormatErrors( sqlsrv_errors() ) ); }
/* Get the first field - the identity from INSERT. */
$photoID = sqlsrv_get_field($uploadPic, 0);
/* Associate the new photoID with the productID. */
$tsql = "UPDATE Production.ProductProductPhoto
SET ProductPhotoID = ?
WHERE ProductID = ?";
$params = array($photoID, $_REQUEST['productid']);
if( sqlsrv_query($conn, $tsql, $params) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
The required parameter for the sqlsrv_next_result function is a PHP resource corresponding to an active statement. It is not necessary to call sqlsrv_next_result to access the first result of a statement. This function will return null if there are no more results on the statement.
For more information about moving through results, see How to: Work with Multiple Result Sets and How to: Detect Empty Result Sets in the product documentation.
Handling Errors and Warnings
The SQL Server 2005 Driver for PHP provides the sqlsrv_errors function for retrieving details about errors and warnings. If an error occurs in any sqlsrv function, the function returns false and the error details are added to the error collection. The sqlsrv_errors function provides access to this error collection.
Note By default, warnings are treated as errors with a few exceptions: warnings that correspond to the SQLSTATE values 01000, 01001, 01003, and 01S02 are never treated as errors. This default behavior can be changed so that warnings are not treated as errors. For more information, see How to: Configure Error and Warning Handling in the product documentation and the Configuring the Driver section.
The general pattern for using sqlsrv_errors is to check the return value of a sqlsrv function and then handle errors accordingly. This code from the Example Application in the product documentation demonstrates the pattern:
if( sqlsrv_execute($insertReview) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }
The sqlsrv_errors function returns a collection of arrays, one array for each error that occurred. Each array contains detailed error information. The custom function FormatErrors in the Example Application simply iterates through the collection of arrays and displays error information:
function FormatErrors( $errors )
{
/* Display errors. */
echo "Error information: <br/>";
foreach ( $errors as $error )
{
echo "SQLSTATE: ".$error['SQLSTATE']."<br/>";
echo "Code: ".$error['code']."<br/>";
echo "Message: ".$error['message']."<br/>";
}
}
When evaluating the return value of a sqlsrv function, it is best to use the PHP triple equals operator (===). This is because all sqlsrv functions return false if an error occurs. For sqlsrv functions that could return some value that PHP evaluates to false it is important to use the triple equals operator to force a literal comparison. For example, sqlsrv_fetch could return null if there are no more rows in a result set. In this case, using a double equals operator (==) to check for an error ($result == false) would evaluate to true, resulting in unexpected program flow.
For more information, see Handling Errors and Warnings in the product documentation.
Resources
The following resources are available for developing applications with the SQL Server 2005 Driver for PHP:
· Download site: SQL Server 2005 Driver for PHP in the Microsoft Download Center
· Peer-to-peer support: SQL Server Driver for PHP in the MSDN Forums
· Online documentation: SQL Server 2005 Driver for PHP Documentation in MSDN Library
· Source code: Microsoft SQL Server 2005 Driver for PHP in CodePlex
Conclusion
The SQL Server 2005 Driver for PHP provides fast and reliable access to SQL Server data using PHP. The driver leverages both Microsoft and PHP technologies (such as Windows Authentication, ODBC connection pooling, and PHP streams) to enable the development of rich PHP Web applications.
For more information:
SQL Server Web site: https://www.microsoft.com/sqlserver/
SQL Server TechCenter: https://technet.microsoft.com/en-us/sqlserver/
SQL Server DevCenter: https://msdn.microsoft.com/en-us/sqlserver/
Data Platform DevCenter: https://msdn.microsoft.com/en-us/data/
Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:
· Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
· Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?
This feedback will help us improve the quality of white papers we release. Send feedback.