PHP SQL Query is not returning anything using bind_param (returning false)

Question

I have a register PHP function that registers a user like so

//Function to create a new user
public function register($firstName, $lastName, $email, $state, $city, $password, $zipCode, $isHMan, $skills, $experience)
{
    $password = md5($password);
    $stmt = $this->conn->prepare("INSERT INTO handyman(firstName, lastName, email, state, city, password, zipCode, isHMan, skills, experience) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("ssssssssss", $firstName, $lastName, $email, $state, $city, $password, $zipCode, $isHMan, $skills, $experience);
    $result = $stmt->execute();
    $stmt->close();
    if ($result) {
        return true;
    } else {
        return false;
    }
}

This function works perfectly when called and everything is inserted into my handyman table (including the md5 hashed password). My problem is with my login function. This is the function:

public function login($email, $password) {
    $password1 = md5($password);
    $stmt = $this->conn->prepare("SELECT * FROM `handyman` WHERE email = ? AND password = ?");
    $stmt->bind_param("ss", $email, $password1);
    $result = $stmt->execute();
    $stmt->close();
    if (mysqli_num_rows($result) != 0) {
        echo "results acquired";
        return true;
    } else {
        echo "no results";
        return false;
    }
}

When I run this I get this warning:

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

And the program outputs no results.
After doing a search here on stackoverflow, people were saying that this is caused by an error in the SQL query and the $result variable actually is returning a false boolean value. I input the query directly into my database with an email and password from my database and it executed perfectly. I cannot for the life of me figure out what is wrong. My values and SQL query seem to be correct. I am guessing it may have something to do with the bind_param function but I don't know what. Any help would be appreciated.

score 1 · Accepted Answer · answered Mar 09 '18 at 22:00

You have mixed procedural style mysqli functions with object oriented.

You'll need to adjust that code you have to this:

public function login($email, $password) {
    $password1 = md5($password);  // don't do this btw
    $stmt = $this->conn->prepare("SELECT * FROM `handyman` WHERE email = ? AND password = ?");
    $stmt->bind_param("ss", $email, $password1);
    $stmt->execute();
    $stmt->store_result();
    if ($stmt->num_rows != 0) {
        echo "results acquired";
        return true;
    } else {
        echo "no results";
        return false;
    }
}

Note: In order to check num_rows you need to have called store_result first.

You also need to alter your register method too since its looking at a failure bool from execute:

public function register($firstName, $lastName, $email, $state, $city, $password, $zipCode, $isHMan, $skills, $experience)
{
    $password = md5($password); // ahem .. no
    $stmt = $this->conn->prepare("INSERT INTO handyman(firstName, lastName, email, state, city, password, zipCode, isHMan, skills, experience) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("ssssssssss", $firstName, $lastName, $email, $state, $city, $password, $zipCode, $isHMan, $skills, $experience);
    $stmt->execute();
    $stmt->store_result();
    if ($stmt->affected_rows) {
        return true;
    } else {
        return false;
    }
}

Note: Using affected_rows also needs store_result to be called. Don't check on the bool result of execute since that is used for failures in sql.

PASSWORDS:

Please look into password_hash() and password_verify() for your storage and login procedures. md5 is insecure, and out dated. Its beyond the scope of this Q/A to provide the full working usage of those functions. Please do look into them though.

PHP SQL Query is not returning anything using bind_param (returning false)

1 Answers1