Reports the number of rows in a result set.
sqlsrv_num_rows( resource $stmt )
$stmt: The result set for which to count the rows.
false if there was an error calculating the number of rows. Otherwise, returns the number of rows in the result set.
sqlsrv_num_rows requires a static or keyset cursor, and will return false if you use a forward cursor or a dynamic cursor. (A forward cursor is the default.) For more information about cursors, see sqlsrv_query and Specifying a Cursor Type and Selecting Rows.
<?php
$server = "server_name";
$conn = sqlsrv_connect( $server, array( 'Database' => 'Northwind' ) );
$stmt = sqlsrv_query( $conn, "select * from orders where CustomerID = 'VINET'" , array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$row_count = sqlsrv_num_rows( $stmt );
if ($row_count === false)
echo "\nerror\n";
else if ($row_count >=0)
echo "\n$row_count\n";
?>
Other Resources
When a cursor (ie SQLSRV_CURSOR_KEYSET or STATIC) is set in the db connection options, as is required, the following notice is returned without a num_row result:
Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 16954 [code] => 16954 [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor. [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor. ) )
Obviously, I'm great at neither PHP or T_SQL so really need help with this. So far I've come across three options which I wasn't too keen on trying either because they aren't quite effiecient or I lack the know-how / control of the SQL database:
- using a "count()" function
- passing a parameter from containing row count through the SP;
- creating a VIEW for the results of the SP, then applying sqlsrv_num_rows() on this VIEW
$errorval = 0; $rowcount = 0;$server = "server_name";
$tsql ='select * from orders where CustomerID = \'VINET\'; SELECT @rowcountEX = @@rowcount, @errorvalEX = @@error';
$sqlcmd = '{call sp_executesql(?, ?, ?, ?)}';
$paramdefs = '@rowcountEX INT OUTPUT, @errorvalEX INT OUTPUT';
$params = array(
array($tsql, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), SQLSRV_SQLTYPE_NVARCHAR(100) ),
array($paramdefs, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), SQLSRV_SQLTYPE_NVARCHAR(100) ),
array($rowcount, SQLSRV_PARAM_OUT ),
array($errorval, SQLSRV_PARAM_OUT )
);$conn = sqlsrv_connect( $server, array( 'Database' => 'Northwind' ) );$stmt = sqlsrv_query( $conn, $sqlcmd, $params );
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) {
$response[] = $row;
}
sqlsrv_next_result( $stmt ); //need to call this to get the SP OUTPUT params loaded into the PHP variables if the SP returned a result set.
sqlsrv_free_stmt( $stmt );sqlsrv_close( $conn );
return array( 'rowcount' => $rowcount, 'rows' => $response);
I’ve had some queries (such as WITH…SELECT) return -1. I assume that this simply means that the number of rows cannot be determined but there are rows. Therefore, do not use the greater-than sign to determine if rows are present, as this function can return a negative value which would obviously evalute to false if you do, even though sqlsrv_query will still return a result.