sqlsrv_fetch_array
Retrieves the next row of data as a numerically indexed array, associative array, or both.
Syntax
sqlsrv_fetch_array( resource $stmt[, int $fetchType [, row[, ]offset]])
Parameters
$stmt: A statement resource corresponding to an executed statement.
$fetchType [OPTIONAL]: A predefined constant. This parameter can take on one of the values listed in the following table:
Value |
Description |
|---|---|
SQLSRV_FETCH_NUMERIC |
The next row of data is returned as a numeric array. |
SQLSRV_FETCH_ASSOC |
The next row of data is returned as an associative array. The array keys are the column names in the result set. |
SQLSRV_FETCH_BOTH |
The next row of data is returned as both a numeric array and an associative array. This is the default value. |
row [OPTIONAL]: Added in version 1.1. One of the following values, specifying the row to access in a result set that uses a scrollable cursor. (When row is specified, fetchtype must be explicitly specified, even if you specify the default value.)
SQLSRV_SCROLL_NEXT
SQLSRV_SCROLL_PRIOR
SQLSRV_SCROLL_FIRST
SQLSRV_SCROLL_LAST
SQLSRV_SCROLL_ABSOLUTE
SQLSRV_SCROLL_RELATIVE
For more information about these values, see Specifying a Cursor Type and Selecting Rows. Scrollable cursor support was added in version 1.1 of the Microsoft Drivers for PHP for SQL Server.
offset [OPTIONAL]: Used with SQLSRV_SCROLL_ABSOLUTE and SQLSRV_SCROLL_RELATIVE to specify the row to retrieve. The first record in the result set is 0.
Return Value
If a row of data is retrieved, an array is returned. If there are no more rows to retrieve, null is returned. If an error occurs, false is returned.
Based on the value of the $fetchType parameter, the returned array can be a numerically indexed array, an associative array, or both. By default, an array with both numeric and associative keys is returned. The data type of a value in the returned array will be the default PHP data type. For information about default PHP data types, see Default PHP Data Types.
Remarks
If a column with no name is returned, the associative key for the array element will be an empty string (""). For example, consider this Transact-SQL statement that inserts a value into a database table and retrieves the server-generated primary key:
INSERT INTO Production.ProductPhoto (LargePhoto) VALUES (?);
SELECT SCOPE_IDENTITY()
If the result set returned by the SELECT SCOPE_IDENTITY() portion of this statement is retrieved as an associative array, the key for the returned value will be an empty string ("") because the returned column has no name. To avoid this, you can retrieve the result as a numeric array, or you can specify a name for the returned column in the Transact-SQL statement. The following is one way to specify a column name in Transact-SQL:
SELECT SCOPE_IDENTITY() AS PictureID
If a result set contains multiple columns without names, the value of the last unnamed column will be assigned to the empty string ("") key.
Example
The following example retrieves each row of a result set as an associative array. The example assumes that the SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console when the example is run from the command line.
<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
echo "Could not connect.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Set up and execute the query. */
$tsql = "SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName='Alan'";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false)
{
echo "Error in query preparation/execution.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Retrieve each row as an associative array and display the results.*/
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{
echo $row['LastName'].", ".$row['FirstName']."\n";
}
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>
The following example retrieves each row of a result set as a numerically indexed array.
The example retrieves product information from the Purchasing.PurchaseOrderDetail table of the AdventureWorks database for products that have a specified date and a stocked quantity (StockQty) less than a specified value.
The example assumes that SQL Server and the AdventureWorks database are installed on the local computer. All output is written to the console when the example is run from the command line.
<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "(local)";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
echo "Could not connect.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Define the query. */
$tsql = "SELECT ProductID,
UnitPrice,
StockedQty
FROM Purchasing.PurchaseOrderDetail
WHERE StockedQty < 3
AND DueDate='2002-01-29'";
/* Execute the query. */
$stmt = sqlsrv_query( $conn, $tsql);
if ( $stmt )
{
echo "Statement executed.\n";
}
else
{
echo "Error in statement execution.\n";
die( print_r( sqlsrv_errors(), true));
}
/* Iterate through the result set printing a row of data upon each
iteration.*/
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))
{
echo "ProdID: ".$row[0]."\n";
echo "UnitPrice: ".$row[1]."\n";
echo "StockedQty: ".$row[2]."\n";
echo "-----------------\n";
}
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>
The sqlsrv_fetch_array function always returns data according to the Default PHP Data Types. For information about how to specify the PHP data type, see How to: Specify PHP Data Types.
If a field with no name is retrieved, the associative key for the array element will be an empty string (""). For more information, see sqlsrv_fetch_array.
See Also
Concepts
About Code Examples in the Documentation
Other Resources
SQLSRV Driver API Reference (Microsoft Drivers for PHP for SQL Server)