How to: Retrieve Data as an Array
The SQL Server Driver for PHP provides the sqlsrv_fetch_array function for retrieving a row of data as an array. This topic provides two examples for retrieving row data as an array:
The first example examines how to use sqlsrv_fetch_array to retrieve a row of data as a numerically indexed array.
The second example examines how to use sqlsrv_fetch_array to retrieve a row of data as an associative array.
Note
By default, sqlsrv_fetch_array retrieves an array with both numeric and associative keys.
Example
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 or How to: Retrieve a Single Field.
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.
The next example retrieves each row of a result set as an associative array. Keys for the array correspond to column names of the result set.
The example retrieves product information from the Purchasing.PurchaseOrderDetail table of the AdventureWorks database for products that have a stocked quantity (StockQty) less than a specified value and a specified due date (DueDate).
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. Note that the returned array is an associative array with
keys corresponding to column names of the result set. */
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{
echo "ProdID: ".$row['ProductID']."\n";
echo "UnitPrice: ".$row['UnitPrice']."\n";
echo "StockedQty: ".$row['StockedQty']."\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 or How to: Retrieve a Single Field.
See Also
Concepts
Comparing Data Retrieval Functions
About Code Examples in the Documentation