处理没有从准备好的select语句返回的结果

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.

I'm new to using prepared statements, but my understanding of it so far is 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. I'm probably missing something obvious here but I've been trying to solve this all morning and it's sending me insane.

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

Hope that helps you solve your problem, happy coding.