Handling no results returned from a prepared select statement

Question

I'm trying to make a basic login system. User enters username + password in a form which is then posted and used in the following code:

if(isset($_POST['submit'])) {


$sql = "SELECT username, password FROM tbl_users WHERE username = ? AND password = ?";
$stmt = $mysqli->prepare($sql);

if(!$stmt) {
    die("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
}


$username = $_POST['username'];
$password = $_POST['password'];

var_dump($username);
var_dump($password);


$bind_result = $stmt->bind_param("ss", $username, $password);
if(!$bind_result) {
    echo "Binding failed: (" . $stmt->errno . ") " . $stmt->error;
}

$execute_result = $stmt->execute();
if(!$execute_result) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

$stmt->bind_result($returned_username, $returned_password);

while($stmt->fetch()) {

    var_dump($returned_username);
    var_dump($returned_password);


    if($username === $returned_username && $password === $returned_password) {
        echo "You are now logged in!";
    } else {
        echo "Incorrect username or password";
    }
}

If I type in the correct username and password, everything works fine and it logs in correctly. The problems start to arise when I enter incorrect information, either using the wrong password or a username that isn't in the database. The "incorrect username or password" line has never worked at all.

My understanding of prepared statements are that once I bind the results, I can only access them in the while loop using fetch. However, if no results are found, that loop will never run, so I have no way of testing whether the query found any results or not.

Answer 1

The problem you're having is based in the logic implemented in your code. If no user is retrieve from the database then the code that checks for the correct username and password is never run, just by changing a few lines of your code you can fix that, try this:

//this is your code at the moment

while($stmt->fetch()) {

    var_dump($returned_username);
    var_dump($returned_password);


    if($username === $returned_username && $password === $returned_password) {
        echo "You are now logged in!";
    } else {
        echo "Incorrect username or password";
    }
}

//replace that for this

while($stmt->fetch()) {

    var_dump($returned_username);
    var_dump($returned_password);

}

if($username === $returned_username && $password === $returned_password) {
    echo "You are now logged in!";
} else {
    echo "Incorrect username or password";
}