Checking for email in the database?

Question

I have this function or method in my class, but it does not return the number of rows when I insert an email into it. I tested the mysql connection, etc. They all work. Also note that the email I pass through this method already exists in the database. my plan is to get the number of rows, and if it is greater than zero, it means that we already have this letter in the 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; } 

Answer 1

 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 will notice SELECT COUNT(*) AS num_rows (I used num_rows, so you did not need to change the code, it could be anything if you refer to it as $stmt->VAR_NAME after executing the request.)

Answer 2

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

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

In the above request, email_exists will be either 1 or 0, depending on whether the mail 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 will let you know how to do it yourself with your own class.