Check if email address already exists if so show message if not then add to table

Question

Hey guys,

I was hoping to get a little insight on this.

What I have is a form collecting firstname, lastname, city, state, and email address. This form is using jquery validation plugin and the form plugin.

I would like to check if email already exists... if so then spit out a message that tells them they already exist.

This is what I have for my update.php script in which the form if using to add names to the mysql database:

<?php
$con = mysql_connect("host","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("table", $con);

$sql="INSERT INTO wallnames (ID, firstname, lastname, city, state, email)
 VALUES('NULL','$_POST[firstname]','$_POST[lastname]','$_POST[city]','$_POST[state]','$_POST[email]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
 }

 echo "<p style=\width:350px; height:200px; vertical-align:middle;\><strong>Thank you for adding your info</strong></p>";

mysql_close($con);
?>

Answer 1

You should create a unique index on the email column, then the insert will fail if you try to insert the same email twice.

CREATE UNIQUE INDEX ux_wallnames_email ON wallnames (email)

You can also run this query to test if an email already exists:

SELECT EXISTS (SELECT NULL FROM wallnames WHERE email = '[email protected]')

It will return either 0 if the email is unused, or 1 if it already exists in the table.