sqlsrv_num_rows Not Returning Any Value

Question

I am trying to get the number of rows returned in a query. The while loop looping through the results works, but for some reason the sqlsrv_num_rows does not return any value:

$result = "SELECT * from dtable WHERE id2 = 'apple'";
$query = sqlsrv_query($conn, $result);

$row_count = sqlsrv_num_rows($query);
echo $row_count;

while($row = sqlsrv_fetch_array($query))
{
      echo 'yes';
}

Thanks.

Answer 1

It is because sqlsrv_query() uses SQLSRV_CURSOR_FORWARD cursor type by default. However, in order to get a result from sqlsrv_num_rows(), you should choose one of these cursor types below:

• SQLSRV_CURSOR_STATIC
• SQLSRV_CURSOR_KEYSET
• SQLSRV_CURSOR_CLIENT_BUFFERED

For more information, check: Cursor Types (SQLSRV Driver)

In conclusion, if you use your query like:

$query = sqlsrv_query($conn, $result, array(), array( "Scrollable" => 'static' ));

you will get result in:

$row_count = sqlsrv_num_rows($query);