How to check the duplicate record in the database?
I created a form on the site that grabs several fields, including "email", and submits it to the database I have with a table called "data". I am using the jQuery Validate plugin to make sure all fields are complete, although I cannot duplicate the email addresses in the db.
The plugin comes with a script called emails.php that it calls when validating the field data, and here's the code:
<?php
$request = trim(strtolower($_REQUEST['email']));
$emails = array('steam@valve.com', 'bill@gates.com', 'blocked@email.com');
$valid = 'true';
foreach($emails as $email) {
if( strtolower($email) == $request )
$valid = '"Thats already taken."';
}
echo $valid;
?>
This snippet only checks the listed emails. Is there a way to query the DB to search the "data" of the table and check the "emails" to see if there is a duplicate and hopefully an error message if there is one?
Also, the script seems to work when I use any of the sample emails, so it essentially "works". I just need to configure it to check the DB.
source to share
Don't use the erroneous "SELECT before INSERT" approach. There will always be a race condition between your "SELECT" and the next "INSERT" unless you are in a global transaction and I doubt it.
You should put the UNIQUE constraint on the email column and then just try to WRITE the new record and catch the SQL uniqueness error. You can catch the error by checking the error code for MySQL, for example it is 1062 or 23000 depending on the driver.
source to share
You just need to write a query to check if the email is in the table:
$query = SELECT count(*) AS `total` FROM `data` WHERE `email` = '{$request}'
$row = mysql_fetch_assoc(mysql_query($query));
if($row['total']) echo false; //exists
else echo true; //doesn't exist
change means "echo", not "return"
source to share
Maybe you can do php code that checks the DB for existing values using the mysql_num_rows command.
Something like that:
$result = mysql_query("SELECT email from accounts where email = $email_to_check");
$found = mysql_num_rows($result);
if ($found > 0) { echo "Email already exists"; }
else .....
source to share