mysqli_num_rows($result)==0 returning error

Question

I am trying to create a change password page with this php script. I want to be able to tell the user whether or not they have put in the correct username and password so that it may be changed. When I check the rows, regardless if it returns rows or not, I always get this error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

This is my code:

<?php

$uname = mysqli_real_escape_string($conn, $_POST['uname']);
$currentpw = mysqli_real_escape_string($conn, $_POST['currentpw']);
$newpw1 = mysqli_real_escape_string($conn, $_POST['newpw1']);
$newpw2 = mysqli_real_escape_string($conn, $_POST['newpw2']);

$sql = "UPDATE USERS
        SET password = '$newpw1'
        WHERE username = '$uname' and password = '$currentpw'";

$result = mysqli_query($conn, $sql);
$row = mysqli_num_rows($result);

    if($row > 0 && $newpw1 == $newpw2){
        mysqli_query($conn, $sql) or die(mysqli_error($conn, $sql));
    }   

    else if($newpw1 != $newpw2){
        echo "Passwords do not match";

    }
    else {
        echo "Username or Password is incorrect";
    }
}

?>

note: i do make a connection before hand just doesn't seem necessary to have it on here. Also if I do enter the right information it will change the password as needed but still have the error message

You need to check the affected rows instead, the number of rows of for select statements. Also, with the code you have now, you appear to attempt to run the same query twice? — Qirel, Mar 15 '19 at 16:37
When in doubt, read the documentation: http://php.net/manual/en/mysqli.affected-rows.php — Bill Karwin, Mar 15 '19 at 16:38
2 bugs: SQL UPDATE has no results. And more important: You'll store clear text passwords??? It's absolute careless and in some countries illegal. — Wiimm, Mar 15 '19 at 16:44
No thats my next step on the password thing. It's bare bones right now for the most part. — egar, Mar 15 '19 at 16:45

score 1 · Answer 1 · answered Mar 15 '19 at 16:40

Select query only have mysqli_num_rows()

The mysqli_num_rows() function returns the number of rows in a result set.

So use mysqli_affected_rows($conn)

The mysqli_affected_rows() function returns the number of affected rows in the previous SELECT, INSERT, UPDATE, REPLACE, or DELETE query.

score 1 · Accepted Answer · answered Mar 15 '19 at 16:47

You need to check the number of affected rows, instead of the number of rows. The num_rows constant is the number of selected rows, while the affected_rows constant is for for insert, update and delete queries.

You also should use a prepared statement instead of injecting variables directly in the query.

Another thing you had was that you attempted to run the same query twice, if it could be run once. That doesn't make much sense.

Here's a revised version, though there's still one important thing missing: you store passwords in planintext! Which is a HUGE security issue!

Don't store your passwords in plain-text! This is not secure at all! PHP has built-in functions which you should use to handle storing of passwords, see the password_hash() function which is a lot more secure!

if (isset($_POST['newpw1'])) {
    $username = $_POST['uname'];
    $current_password = $_POST['currentpw'];
    $newpw1 = $_POST['newpw1'];
    $newpw2 = $_POST['newpw2'];

    if ($newpw1 === $newpw2) {
        $stmt = $conn->prepare("UPDATE users SET password=? WHERE username=? AND password=?");
        $stmt->bind_param("sss", $newpw1, $username, $current_password);
        $stmt->execute();

        if ($stmt->affected_rows) {
            echo "Password updated successfully!";
        } else {
            echo "Username or password is incorrect";
        }
        $stmt->close();
    } else {
        echo "Passwords do not match";
    }
} else {
    // Form was not sent
}

http://php.net/mysqli-stmt.affected-rows

mysqli_num_rows($result)==0 returning error

2 Answers2