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.

`mysqli_num_rows($result)` from `$result = $stmt->execute();` ?? Don't mix procedural and object method together. You need `$stmt->store_result();` and then `$stmt->num_rows` — IncredibleHat, Mar 09 '18 at 21:53
so if i use the $stmt->store_result() and then call $stmt->num_rows in the if statement it should give me the correct functionality? @IncredibleHat — Haki Dehari, Mar 09 '18 at 21:55
@FunkFortyNiner thanks for your earlier comment today, which made me have all this fresh in my mind to answer on ;) — IncredibleHat, Mar 09 '18 at 22:06
@IncredibleHat errr... welcome *lol* - what was it again? short term memory's a tad shot. — Funk Forty Niner, Mar 09 '18 at 22:08
@FunkFortyNiner md5 is used if god-forbid, someone gets direct access to my database then they will not be able to see the plaintext passwords of the user database. md5 encrypted strings cannot be decrypted (of course unless the password is a password that is just a word from the dictionary). — Haki Dehari, Mar 09 '18 at 22:16
you're probably better off with plain text; MD5 is like over 30 years old and has been "broken". You should look up "md5 rainbow tables" ;-) — Funk Forty Niner, Mar 09 '18 at 22:17

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.

thank you very much this solved it! I guess I need to be careful of mixing procedural and object-oriented functions. — Haki Dehari, Mar 09 '18 at 22:03
Yes, be very careful :) Object oriented is less verbose anyway, and is the preferred way of handling it. — IncredibleHat, Mar 09 '18 at 22:04

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

1 Answers1