sqlsrv array doesn't return all rows

Question

I can't seem to figure out why sqlsrv_fetch_assoc only returns 1 row when there are 2 rows in the result set according to sqlsrv_num_rows. I've tried to hard code the parameter, but I still get the same result.

SSMS Result Set

id     description
------ -------------
2      Administrator
3      User

PHP

$col = 'abcd';
$stmt = "SELECT id, [description] FROM dbo.tbl WHERE col = ?";
$params = array( $col );
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );

$query = sqlsrv_query( $conn, $stmt, $params, $options );
if( $query === false ) {
    print( print_r( sqlsrv_errors() ) );
}

while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) {
    ...
}

When I try to view the result set

$row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC );

print_r($row);

I receive

Array
(
    [id] => 2
    [description] => Administrator
)

sqlsrv_num_rows

echo sqlsrv_num_rows( $query );
//Returns 2

Answer 1

When I try to view the result set

      $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC );
      print_r($row);`

Since you listed this separately, I hope you're not doing this (showing all code):

$col = 'abcd';
$stmt = "SELECT id, [description] FROM dbo.tbl WHERE col = ?";
$params = array( $col );
$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );

$query = sqlsrv_query( $conn, $stmt, $params, $options );
if( $query === false ) {
    print( print_r( sqlsrv_errors() ) );
}

while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) {
    ...
    $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC );
    print_r($row);
}

Because then it's obvious you're consuming sqlsrv_fetch_array twice per loop iteration, once for the condition check and once inside the loop.

Remove all fluff from the while loop and have just this - and absolutely nothing else, not even the ... or comments.

while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) {
    print_r($row);
}

Answer 2

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

Emphasis by me.

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

You'll need to do a while loop to fetch all records, like this:

while ($row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) {
    //process $row
}