PHP Checking if the email already exists in the database

Question

I am making a website with a login system. When the user wants to sign up and enters his email, there should not exist one in the MySQL database already. I am trying to make my code through prepared statements.

When the user enters an email that already exists in the database I want it to send the user back to the same signup page with the header function with some kind of error. I tried to store the number of rows in a variable called $resultcheck and check whether or not there are columns that have the same email more than 0 (if it already exists).

Here is the code:

$query = "SELECT * FROM users WHERE Mail=?;";
        $stmt = mysqli_stmt_init($conn);
        if(!mysqli_stmt_prepare($stmt, $query))
        {
            header("Location: ../registrering.php?error=sqlerror");
            exit();
        }
        else
        {
            mysqli_stmt_bind_param($stmt, "s", $mail);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_store_result($stmt);
            $resultCheck = mysqli_stmt_num_rows($stmt);
            if($resultcheck > 0)
            {
                header("Location: ../registrering.php?error=emailtaken");
                exit();
            }
            else {...}

When I submitted however an account with an already existing email in the database then I succesfully entered another column in the table users and have more than one column with the same email.

score 2 · Accepted Answer · answered Apr 28 '19 at 01:01

2

You are assigning $resultCheck and then testing for another variable called $resultcheck which will always be 0.

So your mistake is a typo in your variable naming.

answered Apr 28 '19 at 01:01

TimBrownlaw

5,457
3
24
28

Buh13246 · Answer 2 · 2019-04-28T01:03:52.340

0

Beside of checking with a SELECT SQL. You should set the column in mysql to unique. See https://www.w3schools.com/sql/sql_unique.asp.

edited Apr 28 '19 at 01:03

answered Apr 28 '19 at 00:59

Buh13246

173
7

He does check if there is already one entry with that email using a simple select, and it doesn't seem to work. – theblackips Apr 28 '19 at 01:00
@theblackips your right i'm sry about that. i seem to be half asleep already sry... i removed the dump part... – Buh13246 Apr 28 '19 at 01:05
What is the benefit of using a unique constraint if it is done by the php code? – NIGHTMARE Apr 28 '19 at 01:10
1

maybe sometimes you want to make an import and have an email adress in the sql dump that is already used. Than you dont want to have multiple data. The Unique constrain gives you a safety that there's always only one.. even if you do mistakes in your future Code or actions... Maybe there are also indexing pros but i don't know anything about the mysql indexing system. I found a post who someone says something about that: – Buh13246 Apr 28 '19 at 01:17
1

@NIGHTMARE https://www.quora.com/What-is-the-purpose-of-a-unique-key-in-SQL-Why-is-it-used "Having a unique constraint has an added advantage of creating an index, if you need one, for that column. This will change the access time of that column from O-n to O-1 (if hash table index) or (<) O-log N (if B-Tree index) since the index will be used to reach a value in that column, should your queries say 'where column-value = '''." - Jeetendra Dhall, Mar 11, 2016 – Buh13246 Apr 28 '19 at 01:23

PHP Checking if the email already exists in the database

2 Answers2