Checking if email exists in the database?

Question

I have this function or method in my class, but it doesn't return number of rows when I insert an email into it. I have tested the mysql connection etc, they all are working. Also note that the email that I am passing through the method already exist in database. my plan is to get the number of the rows and if it is bigger than zero, it means we already have this email in database.

public function userExist($email) {
    $query = "SELECT email FROM " . USER_TABLE . " WHERE email = ?";

    $stmt = $this->_db->prepare($query);

    $stmt->bind_param("s", $email);

    if ($stmt->execute()) {
        $stmt->store_result();
        return $stmt->num_rows;
    }
        return false;


}

You don't need to `select email` as long as it always equals to `$email`. Perform `SELECT COUNT(*)` better — zerkms, Feb 22 '12 at 20:42
Everything *seems* fine. Is `display_errors` set to true, or are errors logged? — Josh, Feb 22 '12 at 20:48
I am not familiar with OOP way of debugging and that is my main problem, I get frustrated by programming OOP way. I use eclipse to do the coding and makes coding so much easier but I need to learn how to debug. Do you know a good debugging tool or source for php? — Sokhrat Sikar, Feb 22 '12 at 21:02

Marwelln · Answer 1 · 2012-02-22T20:48:29.023

2

I don't know how to use pdo with mysqli, but what you want to do is check if entry exists using mysql EXISTS function and than fetch the result (which is allways 1 or 0).

SELECT EXISTS(SELECT 1 FROM ".USER_TABLE." WHERE email = ?) as email_exists

In the query above, email_exists will be either 1 or 0, depending on wether the email already is in the table or not.

With PDO I would do it like this:

$sth = $dbh->prepare("SELECT EXISTS(SELECT 1 FROM ".USER_TABLE." WHERE email = :email)");
$sth->execute(array('email' => $email));
return $sth->fetchColumn();

I'll let you figure out how to do it yourself with your own class.

edited Feb 22 '12 at 20:48

answered Feb 22 '12 at 20:42

Marwelln

28,492
21
93
117

Thanks for the hack, but I would rather know, what is it that I am messing up with that doesn't return the mysqli num rows... – Sokhrat Sikar Feb 22 '12 at 20:50

score 1 · Accepted Answer · edited Nov 19 '13 at 21:11

public function userExist($email) {
$query = "SELECT COUNT(*) AS num_rows FROM " . USER_TABLE . " WHERE email = ?";

$stmt = $this->_db->prepare($query);

$stmt->bind_param("s", $email);

if ($stmt->execute()) {
    $stmt->store_result();
    return $stmt->num_rows;
}
    return false;


}

there you go, notice SELECT COUNT(*) AS num_rows (I used num_rows so you wouldn't have to change your code, this could be anything so long as you reference it like $stmt->VAR_NAME after you execute the query.)

Checking if email exists in the database?

2 Answers2