Sqlsrv fetch array php

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 statement 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.

Associative array 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.

"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); ?> 

Indexed array 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.

"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.

Источник

sqlsrv_fetch_array

Возвращает следующую доступную строку данных в виде ассоциативного массива, числового массива или и того и другого (по умолчанию).

Список параметров

Ресурс оператора, возвращаемый sqlsrv_query или sqlsrv_prepare.

Предопределённая константа, указывающая тип возвращаемого массива. Возможные значения: SQLSRV_FETCH_ASSOC , SQLSRV_FETCH_NUMERIC или SQLSRV_FETCH_BOTH (по умолчанию).

Тип выборки SQLSRV_FETCH_ASSOC не следует использовать при использовании набора результатов с несколькими столбцами с одинаковым именем.

Задаёт строку для доступа в результирующем наборе, в котором используется прокручиваемый курсор. Возможные значения: SQLSRV_SCROLL_NEXT , SQLSRV_SCROLL_PRIOR , SQLSRV_SCROLL_FIRST , SQLSRV_SCROLL_LAST , SQLSRV_SCROLL_ABSOLUTE и SQLSRV_SCROLL_RELATIVE (по умолчанию). Если этот параметр указан, fetchType должен быть явно определён.

Указывает строку, к которой будет осуществляться доступ, если для параметра строки установлено значение SQLSRV_SCROLL_ABSOLUTE или SQLSRV_SCROLL_RELATIVE . Обратите внимание, что первая строка в наборе результатов имеет индекс 0.

Возвращаемые значения

В случае успешного выполнения возвращает массив, null , если в наборе результатов больше нет строк и false в случае возникновения ошибки.

Примеры

Пример #1 Получение ассоциативного массива.

$serverName = «serverName\instanceName» ;
$connectionInfo = array( «Database» => «dbName» , «UID» => «username» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );
if( $conn === false ) die( print_r ( sqlsrv_errors (), true ));
>

$sql = «SELECT FirstName, LastName FROM SomeTable» ;
$stmt = sqlsrv_query ( $conn , $sql );
if( $stmt === false ) die( print_r ( sqlsrv_errors (), true ) );
>

while( $row = sqlsrv_fetch_array ( $stmt , SQLSRV_FETCH_ASSOC ) ) echo $row [ ‘LastName’ ]. «, » . $row [ ‘FirstName’ ]. «
» ;
>

Пример #2 Получение числового массива.

$serverName = «serverName\instanceName» ;
$connectionInfo = array( «Database» => «dbName» , «UID» => «username» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );
if( $conn === false ) die( print_r ( sqlsrv_errors (), true ));
>

$sql = «SELECT FirstName, LastName FROM SomeTable» ;
$stmt = sqlsrv_query ( $conn , $sql );
if( $stmt === false ) die( print_r ( sqlsrv_errors (), true ) );
>

while( $row = sqlsrv_fetch_array ( $stmt , SQLSRV_FETCH_NUMERIC ) ) echo $row [ 0 ]. «, » . $row [ 1 ]. «
» ;
>

Примечания

Не указанный fetchType или явное использование константы SQLSRV_FETCH_TYPE в приведённых выше примерах вернёт массив, у которого ключи будут как ассоциативные, так и числовые.

Если более одного столбца возвращается с одним и тем же именем, последний столбец будет иметь приоритет. Чтобы избежать конфликтов имён полей, используйте псевдонимы.

Если возвращается столбец без имени, ассоциативный ключ для элемента массива будет пустой строкой («»).

Смотрите также

• sqlsrv_connect() — Открывает соединение с базой данных Microsoft SQL Server
• sqlsrv_query() — Подготавливает и выполняет запрос
• sqlsrv_errors() — Возвращает информацию об ошибке и предупреждении последней выполненной операции SQLSRV
• sqlsrv_fetch() — Делает следующую строку в наборе результатов доступной для чтения

Источник

sqlsrv_fetch_array

Returns the next available row of data as an associative array, a numeric array, or both (the default).

Список параметров

A statement resource returned by sqlsrv_query or sqlsrv_prepare.

A predefined constant specifying the type of array to return. Possible values are SQLSRV_FETCH_ASSOC , SQLSRV_FETCH_NUMERIC , and SQLSRV_FETCH_BOTH (the default).

A fetch type of SQLSRV_FETCH_ASSOC should not be used when consuming a result set with multiple columns of the same name.

Specifies the row to access in a result set that uses a scrollable cursor. Possible values are SQLSRV_SCROLL_NEXT , SQLSRV_SCROLL_PRIOR , SQLSRV_SCROLL_FIRST , SQLSRV_SCROLL_LAST , SQLSRV_SCROLL_ABSOLUTE and, SQLSRV_SCROLL_RELATIVE (the default). When this parameter is specified, the fetchType must be explicitly defined.

Specifies the row to be accessed if the row parameter is set to SQLSRV_SCROLL_ABSOLUTE or SQLSRV_SCROLL_RELATIVE . Note that the first row in a result set has index 0.

Возвращаемые значения

Returns an array on success, NULL if there are no more rows to return, and FALSE if an error occurs.

Примеры

Пример #1 Retrieving an associative array.

$serverName = «serverName\instanceName» ;
$connectionInfo = array( «Database» => «dbName» , «UID» => «username» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );
if( $conn === false ) die( print_r ( sqlsrv_errors (), true ));
>

$sql = «SELECT FirstName, LastName FROM SomeTable» ;
$stmt = sqlsrv_query ( $conn , $sql );
if( $stmt === false ) die( print_r ( sqlsrv_errors (), true ) );
>

while( $row = sqlsrv_fetch_array ( $stmt , SQLSRV_FETCH_ASSOC ) ) echo $row [ ‘LastName’ ]. «, » . $row [ ‘FirstName’ ]. «
» ;
>

Пример #2 Retrieving a numeric array.

$serverName = «serverName\instanceName» ;
$connectionInfo = array( «Database» => «dbName» , «UID» => «username» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );
if( $conn === false ) die( print_r ( sqlsrv_errors (), true ));
>

$sql = «SELECT FirstName, LastName FROM SomeTable» ;
$stmt = sqlsrv_query ( $conn , $sql );
if( $stmt === false ) die( print_r ( sqlsrv_errors (), true ) );
>

while( $row = sqlsrv_fetch_array ( $stmt , SQLSRV_FETCH_NUMERIC ) ) echo $row [ 0 ]. «, » . $row [ 1 ]. «
» ;
>

Примечания

Not specifying the fetchType or explicity using the SQLSRV_FETCH_TYPE constant in the examples above will return an array that has both associative and numeric keys.

If more than one column is returned with the same name, the last column will take precedence. To avoid field name collisions, use aliases.

If a column with no name is returned, the associative key for the array element will be an empty string («»).

Смотрите также

• sqlsrv_connect() — Opens a connection to a Microsoft SQL Server database
• sqlsrv_query() — Prepares and executes a query.
• sqlsrv_errors() — Returns error and warning information about the last SQLSRV operation performed
• sqlsrv_fetch() — Makes the next row in a result set available for reading

Источник