Checking if email exists in MySQLi database using PHP

Question

I'm trying, as many others here have before me, to check if an email already exists in my database during account registration on my website, using PHP to interact with a MySQLi database. I've tried using as many guides as I could find here but still no luck.

Below is the code I originally wrote to insert the email of users into my database upon registration:

if ($_SERVER["REQUEST_METHOD"] == "POST") {
        $database = new mysqli("localhost", "user", "dbpw", "dbname");
if($database->connect_error){
            die( "Error connecting:" . $database->connect_error);
        }

$email = $database->real_escape_string(htmlspecialchars($_POST["email"]));
$query = "SELECT email FROM Users WHERE email =$email";
$result = $database->query($query);
$numOfRows = $database->num_rows($result);
if($numOfRows > 0){
  echo "Email already exists in our database.";
}else{
$query = "INSERT INTO Users (email) VALUES ('" . $email . "');
if(!$database->query($query)){echo("<p>Unable to add user for query: " . $query . " <br /> Error: " . $database->errno . " " . $database->error);}}

Prior to inserting the email check (the if($numOfRows) statement), my database was being updated properly.

However, after adding my attempted email check, the page completely broke. My database wouldn't update with unique passwords, and it wouldn't display the echoed message "Email already exists in our database" if the email was a duplicate. The page would load, but with just the basic header, footer, and background CSS I have linked to every page.

you have an error in the select query: `$query = "SELECT email FROM Users WHERE email =$email";` should be `$query = "SELECT email FROM Users WHERE email ='$email'";` the email value should be wrapped in single quotes. It is a string. You do so in the right way in the insert — Lelio Faieta, Mar 21 '17 at 10:03
**Warning:** You are open to *mysql injection*! Use `prepared statements` to secure yourself from it. — Nytrix, Mar 21 '17 at 10:13
@Nytrix Roger, thanks for the advice. Will look into changing this file to use prepared statements now that I got the my main problem fixed. — , Mar 21 '17 at 11:39

score 1 · Accepted Answer · answered Mar 21 '17 at 09:59

1

Instead of using

$numOfRows = $database->num_rows($result);

Use

if($result ->num_rows)
{
}

answered Mar 21 '17 at 09:59

Raman

624
5
13

Thank you. I did what you, @codvlpr and @Lelio Faieta suggested and got things working. `if($result->num_rows > 0){ ... }` – Mar 21 '17 at 11:35

score 0 · Answer 2 · answered Mar 21 '17 at 10:03

0

You need to add single quotation marks around the email variable. Like this '$email'. Check the below code.

$query = "SELECT email FROM Users WHERE email = '$email'";

And you need to edit your conditional statement to check whether its greater than or equal to 0 or you can just use variable as boolean in condition block.

answered Mar 21 '17 at 10:03

codvlpr

122
4

I'm confused about that last part. If it is equal to 0, then that should mean the email is not present in my database, correct? In that case I'd want to follow through with the `else` statement. – Mar 21 '17 at 10:48
I didn't mean to confuse you. Ignore that 0 part. That was my mistake! – codvlpr Mar 21 '17 at 10:50

Checking if email exists in MySQLi database using PHP

2 Answers2