Check email to be unique php

Question

I am creating a website about music and at the bottom of my website I have a form that has only email submission. I am doing this in order to gather email from the users.

Moreover, I have created a database and in that database I have created a table called emails with only 2 columns:idand email.

I would like to make the emails that I receive unique, because now that I am testing it I can put many emails with the same name. I have tried some coding in order to achieve this but no luck so I will post only the code I have at the moment, so you can help me with what I need to add in that code in order to receive a unique email address.

P.S I will update my database from mysql to mysqli at the end of the project.

Here is my current code:

<?php 
error_reporting(E_ERROR | E_PARSE);
include('../database/db.php');
$email = $_POST['email'];

if($email != "") {
$sql = mysql_query ("INSERT INTO emails (email) VALUES ('$email')");
echo "Thank you for Submitting. Redirecting back to Home Page";
}
?>

set the column `email` as unique in the database? – Professor Abronsius Feb 01 '16 at 12:59 — Professor Abronsius, Feb 01 '16 at 12:59
...and use INSERT IGNORE, or INSERT... ON DUPLICATE KEY – Strawberry Feb 01 '16 at 13:01 — Strawberry, Feb 01 '16 at 13:01

score 3 · Answer 1 · answered Feb 01 '16 at 13:12

First.. as @RamRaider already commented you must add an unique index to the email column
Second.. you must stop using mysql_* functions because they are deprecated. You may use mysqli_* functions, MySQLi class or PDO. Also, by using prepared statements, you avoid SQL Injections

Even if you set an unique index, you should always verify before inserting it:

<?php

// PDO instantiation here

$stmt = $pdo->prepare('SELECT COUNT(email) AS EmailCount FROM emails WHERE email = :email');
$stmt->execute(array('email' => $_POST['email']));
$result = $stmt->fetch(PDO::FETCH_ASSOC);

if ($result['EmailCount'] == 0) {
    $stmt = $pdo->prepare('INSERT INTO emails (email) VALUES (:email)');
    $stmt->execute(array('email' => $_POST['email']));
    echo 'Thank you for Submitting. Redirecting back to Home Page';
} else {
    echo 'E-mail exists!';
}

score 1 · Accepted Answer · answered Feb 01 '16 at 13:09

error_reporting(E_ERROR | E_PARSE);
include('../database/db.php');
$email = $_POST['email'];
if($email != "") {
    $result = mysql_query("SELECT * FROM emails where email='".$email."'");
    $num_rows = mysql_num_rows($result);
    if($num_rows >= 1){
        echo "email exist";
    }else{
        $sql = mysql_query ("INSERT INTO emails (email) VALUES ('$email')");
        echo "Thank you for Submitting. Redirecting back to Home Page";
    }
}

replace your code with this :)

score 0 · Answer 3 · edited May 23 '17 at 11:45

0

Note: This will not go into how you need to clean your PHP. (Helpful tips)

As @RamRaider suggests, update the email column to be unuiqe.

Then set the result of your mysql_query() to be a variable which can be examined to see if there was a valid execution or not.

$result = mysql_query("INSERT INTO emails (email) VALUES ('$email')");
 if (mysql_num_rows($result)) {
    // Something good happened and email was unuiqu
 }
else{
    // well we have encountered a problem and need to fix that or inform he user
}

As you can see, it will check the result you tried to execute to determine if there was a successful item added.

edited May 23 '17 at 11:45

Community

1
1

answered Feb 01 '16 at 13:03

Pogrindis

7,755
5
31
44

If your database is case sensitive don´t forget to persist all mail addresses in the same case to prevent persisting the same mail address twice in different notation. – Bonscho Feb 01 '16 at 13:10

score 0 · Answer 4 · answered Feb 01 '16 at 13:04

you can check the email before insertion like below

if($email != "") {
$sql = mysql_query ("select * from `emails` where email='".$email."'");
$fetch = mysql_num_rows($sql);
if($fetch>0)
{
echo "email already inserted";
}
else
{
$sql = mysql_query ("INSERT INTO emails (email) VALUES ('$email')");
echo "Thank you for Submitting. Redirecting back to Home Page";
}}

Check email to be unique php

4 Answers4

Linked