SQL – Select doesn’t retrieve results

I’m using sqlsrv_num_rows in order to check if a user exists in the DB.

When i’m running the query in my DB i’m getting 1 result, but in my PHP I’m not getting anything (echo doesn’t print anything). Why is that?

$query = "SELECT TOP 1 id, tourOp FROM users WHERE (valid = 1) AND (email = '".trim($_POST['email'])."') AND (password = '".trim($_POST['password'])."')";
$stmt  = sqlsrv_query( $conn, $query);      

echo "num: ".sqlsrv_num_rows( $stmt );

if (!sqlsrv_num_rows( $stmt )) {
    return (false); 
} else {

}

Example query

SELECT TOP 1 id, name FROM users WHERE (valid = 1) AND (email = 'roi@some_email.com') AND (password = '8521')

I’m using PHP and MSSQL.

Answer

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.