Check If Already In Database

PHP
1

Hi,

I have created a membership script but I have omitted one crucial factor. The script doesn’t check if the email address is already in the database. Can anyone advise how I check if the an input from a field is already in the database?

(I kept entering the same email address whilst testing)

2

Hi,

I’ve now got this which reads if the email address is already in the DB but it doesn’t stop the email address from being inserted twice. Do I put some sort of stop code in there?

$query = mysql_query("SELECT * FROM users WHERE email = '". $registerEmail ."' OR email = '". $email ."'");

if (mysql_num_rows($query) > 0)
{
     echo 'Email Address is Already In Use.';
}
3

Add a unique index on the email column. Then there’s no need to do a select to check the existence of the email address. Just use INSERT IGNORE if you want to ignore the duplicate key error, or do an INSERT and then check the returning error code to handle the duplicate key error.

4

So if I change the key to unique and keep the existing message that will then prevent the email being added to the database and return a message.

Is that right?

5

Hi,

How do I also change the table for email to unique? Im sure Ive seen this but cant seem to find it now.

6

Hi,

I changed the field to unique but it returns this horrible message and blocks out the page.

Duplicate entry ‘someone@yahoo.com’ for key ‘email’

How can I change the code I had to block the insert of duplicate emails?

7

In my opinion that is validation that should occur at the application level rather than the database level. However, if you would like to base it on whether MySQL returns an error or not you would simply check the error code. I have never done it this way myself so had to look up the error code. Searching on MySQL revealed that the error code for a duplicate key entry is 1062. Therefore, you would use something like the below to check and carry out the appropriate action when a duplicate is attempted.


if(mysql_errorno() == 1062) {
   // alert user of duplicate entry
}

Normally when dealing with forms there is some type of validation layer prior to saving data to MySQL. That is where I would carry this out. If you are just dealing with spaghetti code though it probably doesn’t make much of a difference either way. It goes without saying though I would always define a unique key in this case. Perhaps even make the email column the primary key of the table depending on context/purpose of the table.

8

it cannot create the unique index as long as the table actually contains duplicates

remove those first, and create the index again

i disagree with oddz, i would do a single call to the database to insert a new row (and detect whether there was a duplicate error), rather than doing two calls to the database for each insertion (a select first, followed by the insert)

9

Hi,

I have this code which checks if the email address is already in the database however it doesn’t stop the email from being inserted. So its only doing half the job. Can you advise how I can stop the email from being entered?

$query = mysql_query("SELECT * FROM users WHERE email = '". $registerEmail ."' OR email = '". $email ."'"); 

if (mysql_num_rows($query) > 0) 
{ 
     echo 'Email Address is Already In Use.'; 
}
10

Kill the script by using exit in the conditional. That is simplest but least user friendly way of doing so. Ideally you would want to show some type of message to the user telling them that the email already exists.

11

Hi,

I do want to show that the email already exists. Is it possible to kill the script after the message is displayed?