sqlsrv_num_rows does not work

Question

I have a site that is being done with PHP + SQL SERVER, and to log in needs client code, and it puts there only that always says that is incorrect, and that is correct.

Apparently the sqlsrv_num_rows not working, because I put the correct login and does not enter the site I made.

Login validation code:

    <?php 
session_start();

$serverName = "10.0.0.0.0";
$connectionInfo = array( "Database"=>"banco", "UID"=>"usuario", "PWD"=>"senha" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}


$var1 = $_POST['codMont']; 

$sql = "SELECT *
FROM AB7030 AB7,ABB030 ABB,AB6030 AB6, AA1030 AA, SA1030 SA
WHERE   ABB.D_E_L_E_T_ = '' 
AND AB7.D_E_L_E_T_ ='' 
AND AB6.D_E_L_E_T_ = '' 
AND SA.D_E_L_E_T_ = '' 
AND ABB.ABB_FILIAL = AB6.AB6_FILIAL 
AND ABB.ABB_NUMOS = AB6.AB6_NUMOS 
AND ABB.ABB_FILIAL = AB7.AB7_FILIAL
AND ABB.ABB_NUMOS = AB7.AB7_NUMOS 
AND AB6.AB6_CODCLI = SA.A1_COD
AND AA.AA1_CODTEC = ABB_CODTEC
AND ABB.ABB_CODTEC = '".$var1."'  
AND AB7.AB7_TIPO IN ('1','3')   
        AND AB7_FILIAL = '99'";

$stmt = sqlsrv_query($conn, $sql);
$row_count = sqlsrv_num_rows($stmt);

if ($row_count > 0 ){
    $_SESSION['codMont'] = $var1;
    header('location:homee.php');
}else{
    unset ($_SESSION['codMont']);
    $mensagem = "<div class='alert alert-danger'>Código do Montador ou senha incorretos. Tente novamente!</div>";
    printf ($mensagem);
    printf ($var1);
}

Even if I put the correct or incorrect code does not work, always says that has no registration with this code, and yes. Does anyone know what the problem would be?

Answer 1

This is because it uses by default SQLSRV_CURSOR_FORWARD, in accordance with https://docs.microsoft.com/en-us/sql/connect/php/cursor-types-sqlsrv-driver?view=sql-server-2017, as indicated in the link itself:

sqlsrv_num_rows Returns an error for result sets created with this cursor type.

Translating: sqlsrv_num_rows returns an error for the defined output Returns an error for result sets created with this cursor type. (referring to SQLSRV_CURSOR_FORWARD)

This should be defined in sql_query, and the following parameters are supported for Scrollable which must be configured in the third parameter, the $options, as below:

sqlsrv_query ($conn, $sql, array $params, array $options)

The options for Scrollable sane:

• SQLSRV_CURSOR_FORWARD (pattern)

  Lets you move one row at a time, starting at the first line of the result set, until you reach the end of the result set.

• SQLSRV_CURSOR_STATIC

  Allows accessing lines in any order, but will not reflect changes in the database.

• SQLSRV_CURSOR_DYNAMIC

  Allows access lines in any order and will reflect changes in the database.

• SQLSRV_CURSOR_KEYSET

  Allows you to access lines in any order. However, a key set cursor does not update the row count if a row is deleted from the table (an deleted row is returned with no values).

• SQLSRV_CURSOR_CLIENT_BUFFERED, the latter is not informed in php.net (http://php.net/manual/en/function.sqlsrv-query.php), but as php.net itself indicates the microsoft site so I think it works in all current versions (soon I will detail)

  Allows you to access lines in any order. Creates a client-side cursor query (client-side in case it refers to PHP, ie PHP is client of the database server, has nothing to do with front-end, html and css, the process is in PHP itself, the other cursors different from this are on the bank side)

Whenever possible, look at the documentation, there are sessions for examples of almost all Apis, like this http://php.net/manual/en/function.sqlsrv-num-rows.php#example-2656

For your case it must be something like:

$params = array();
$options =  array( 'Scrollable' => SQLSRV_CURSOR_KEYSET );

$stmt = sqlsrv_query($conn, $sql, $params, $options);
$row_count = sqlsrv_num_rows($stmt);

if ($row_count > 0 ){
    $_SESSION['codMont'] = $var1;
    header('location:homee.php');
}else{
    unset ($_SESSION['codMont']);
    $mensagem = "<div class='alert alert-danger'>Código do Montador ou senha incorretos. Tente novamente!</div>";
    printf ($mensagem);
    printf ($var1);
}

Note: sqlsrv_num_rows can return 0 or FALSE, ie will depend on your need, in case FALSE is when something fails, and 0 (zero) occurs when finding zero records, so in your specific case check if it is greater than zero > 0 will work for both zero and false, but in other cases this may not be ideal, especially if you need to know what may have happened, for example:

if ($row_count === false) {
    echo 'Erro ao obter o total';
} else {
    echo "Foram encontrados $row_count resultado(s)";
}