Sqlsrv_Num_Rows Not Returning Any Value

sqlsrv_num_rows Not Returning Any Value

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

sqlssrv_num_rows not returning anything

It is suddenly working

I just removed 'HD' from the query and added it to $params and this seems to have resolved everything.

I am not sure why it is working this way as the final query sent to SQL comes out the same either way I run it, however it does work.

$sql = "SELECT * FROM Question WHERE Category = ?;";
$params = array('HD');  
$options =  array( 'Scrollable' => 'buffered' ); 
$stmt = sqlsrv_query( $conn, $sql, $params, $options ); 
if( $stmt === false ) { reportSQLError($sql, $params); }
$maxHDQ = sqlsrv_num_rows($stmt);

sqlsrv_query not returning anything

The solution that worked for me was to put array("Scrollable" => 'static') as a configuration option for the select function.

Query with data from user in MSSQL with PHP

You have two options:

• Execute sqlsrv_query() with the appropriate cursor type, if you want to get the exact number of the returned rows.
• Use sqlsrv_has_rows() if you want to check if there are rows returned.

PHP code using sqlsrv_num_rows():

<?php
    
   ...
   $result = sqlsrv_query($conn2, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
   $queryResult = sqlsrv_num_rows($result);
   if ($queryResult > 0) {
       // Fetch data
   }

   ...
?>

PHP code using sqlsrv_has_rows():

<?php
    
   ...
   $result = sqlsrv_query($conn2, $sql);
   $queryResult = sqlsrv_has_rows($result);
   if ($queryResult) {
       // Fetch data
   }

   ...
?>

As an additional note, always use parameterized statements to prevent possible SQL injection issues. As is mentioned in the documentation, function sqlsrv_query() does both statement preparation and statement execution and can be used to execute parameterized queries.

<?php

    ...
    $sql = "
        SELECT codepo, codepsa, controle, FORMAT(date, 'dd-MM-yyyy hh:mm:ss') AS date 
        FROM dbo.codebarre 
        WHERE datediff(day, date, ?) = 0
    ";
    $params = array($search);
    $options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
    $result = sqlsrv_query($conn2, $sql, $params, $options);

...
?>

sqlsrv array doesn't return all rows

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);
}

SQL - Select doesn't retrieve results

Explanations:

• Function sqlsrv_num_rows() requires a client-side, static, or keyset cursor, and will return false if you use a forward cursor or a dynamic cursor (the default cursor is forward cursor). Execute sqlsrv_query() with additional $options parameter and set the appropriate cursor type with "Scrollable" => SQLSRV_CURSOR_KEYSET
• Use parameterized statements. Function sqlsrv_query() does both statement preparation and statement execution and can be used to execute parameterized queries.
• If you want to check if the result set has one or more rows, you may use sqlsrv_has_rows().

Example, based on your code:

<?php
$query = "
    SELECT TOP 1 id, tourOp 
    FROM users 
    WHERE (valid = 1) AND (email = ?) AND (password = ?)";
$params = array(trim($_POST['email']), trim($_POST['password']));
$options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
$stmt = sqlsrv_query( $conn, $query, $params, $options);      
if ($exec === false){
    echo print_r( sqlsrv_errors()); 
    echo "<br>";
    return (false);
}

$count = sqlsrv_num_rows($stmt);
if ($count === false) {
    echo print_r( sqlsrv_errors()); 
    echo "<br>";
    return (false);
} else {
    echo "num: ".$count;
}
?>

Notes:

Do not send user credentials in plain text.

---

Related Topics