How would I make my PHP form check for an existing email address using MySQL?

I want to be able to have just one user per email address so that someone else can't cause email spamming.

Is there any way to make this possible with PHP and a MySQL database?
LVL 3
ViRoguesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VenabiliCommented:
Where do you save the emails?
The easiest way will probably be to make the email field in the DB unique... so that it does not allow you to add the same mail twice.

Other from this - just checking of the mail is already in the DB before inserting will be enough.

Or do you mean something else?
0
ViRoguesAuthor Commented:
Thanks for the reply,

I made the email field in the database unique already, but if a user uses the same email, it won't post it to the database.

I want to be able to notify the user that the email address is already being used.
0
VenabiliCommented:
You will need to modify the page so that it first checks with the DB before sending the mail and if the DB says that it is already in use to simply show a message back to the user.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

ViRoguesAuthor Commented:
How would I do that? Sorry, I'm new to PHP.
0
VenabiliCommented:
Depends on how you send the mails. Can you show  your code?
0
ViRoguesAuthor Commented:
Yes, sorry. I should have done that in the first place.
<?php

function VerifyForm(&$values, &$errors)
{

    
    if (strlen($values['first']) < 3)
        $errors['first'] = 'The name you provided is too short.';
    elseif (strlen($values['first']) > 50)
        $errors['first'] = 'The name you provided is too long.';

    if (strlen($values['last']) < 2)
        $errors['last'] = 'The name you provided is too short.';
    elseif (strlen($values['last']) > 50)
        $errors['last'] = 'The name you provided is too long.';

    if (strlen($values['nickname']) < 1)
        $errors['nickname'] = 'The name you provided is too short.';
    elseif (strlen($values['nickname']) > 50)
        $errors['nickname'] = 'The name you provided is too long.';
        
    if (!ereg('.*@.*\..{2,4}', $values['email']))
        $errors['email'] = 'The email address you provided is invalid.';
		
		require_once('recaptchalib.php');
$privatekey = "**CHANGEDforDisplayPurposes*";
$resp = recaptcha_check_answer ($privatekey,
                                $_SERVER["REMOTE_ADDR"],
                                $_POST["recaptcha_challenge_field"],
                                $_POST["recaptcha_response_field"]);

if (!$resp->is_valid) {
  $errors['captcha']="The reCAPTCHA wasn't entered correctly. Go back and try it again.";
}
        
    return (count($errors) == 0);
}

function DisplayForm($values, $errors)
{
    ?>
    <HTML><!-- InstanceBegin template="/Templates/ViRogues Page.dwt.php" codeOutsideHTMLIsLocked="false" -->
<HEAD>
<link rel="stylesheet" type="text/css" href="/VR/VRcss.css" />
<!-- InstanceBeginEditable name="docmeta" -->
<meta name="keywords" content="ViRogues, Jose, Lopez, Very, Random., PHP, MySQL, live, test" />
<meta name="description" content="This is one of the testing pages for ViRogues.com. It implements PHP and Database use." />
<!-- InstanceEndEditable -->
<!-- InstanceBeginEditable name="doctitle" -->
<?PHP
$pagetitle="TEST - PHP with Database";
?>
<!-- InstanceEndEditable -->
<title>ViRogues
<?php if (isset($pagetitle)) {echo " - ".$pagetitle;}?>
</title>
</HEAD>
<body>
<!-- InstanceBeginEditable name="docbody" -->
<div id="VRversion1">
<? echo "v 0.6.3" ?>
</div>
    <?php
    if (count($errors) > 0)
        echo "<p>There were some errors in your submitted form, please correct them and try again.</p>";
    ?>
    
    <form action="<?= $_SERVER['PHP_SELF'] ?>" method="POST">
      <table>
      <tr>
      <td><table>
        <tr>
          <td>First Name:</td>
          <td><input type="text" size="30" name="first" value="<?= htmlentities($values['first']) ?>"/></td>
          <td class="error"><?= $errors['first'] ?></td>
        </tr>
        <tr>
          <td>Last Name:</td>
          <td><input type="text" size="30" name="last" value="<?= htmlentities($values['last']) ?>"/></td>
          <td class="error"><?= $errors['last'] ?></td>
        </tr>
        <tr>
          <td>Preferred Name:</td>
          <td><input type="text" size="30" name="nickname" value="<?= htmlentities($values['nickname']) ?>"/>
          <td class="error"><?= $errors['nickname'] ?></td>
        </tr>
        <tr>
          <td>Email:</td>
          <td><input type="text" size="30" name="email" value="<?= htmlentities($values['email']) ?>"/></td>
          <td class="error"><?= $errors['email'] ?></td>
          <table><td align="center"><?
  require_once('recaptchalib.php');
$publickey = "6LeobAwAAAAAAH97YlavBu33bwp4Znz-4FvOSyhc";
echo recaptcha_get_html($publickey);
?>
</td><td class="error"><?= $errors['captcha'] ?></td>
</table>
        </tr>
          <td colspan="2"><input type="submit" value="Register">
          </tr>
      </table></td>      
      </table>
    </form>
<?php
}

function ProcessForm($values)
{
    	
	$db = mysql_connect("*CHANGEDforDisplayPurposes*", "*CHANGEDforDisplayPurposes*", "*CHANGEDforDisplayPurposes*");
 mysql_select_db("*CHANGEDforDisplayPurposes*",$db);
 
  //for new PHP
   $first=$_POST[first];
   $last=$_POST[last];
   $nickname=$_POST[nickname];
   $email=$_POST[email];
   //
     
 $sql = "INSERT INTO personnel (firstname, lastname, nick, email) VALUES ('$first','$last','$nickname','$email')";
      $result = mysql_query($sql); echo "Thank you! Information entered.";
}

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{
    $formValues = $_POST;
    $formErrors = array();
    
    if (!VerifyForm($formValues, $formErrors))
        DisplayForm($formValues, $formErrors);
    else
        ProcessForm($formValues);
}
else
    DisplayForm(null, null);
?>
<!-- InstanceEndEditable -->
</body>
<?
include 'VRfooter.php';
?><!-- InstanceEnd -->

Open in new window

0
VenabiliCommented:
Ok - inside of the VerifyForm method, connect to the DB and check of the email is already in use. if it is, then add an error to the errors array ("This mail is already in use").

That should be enough.
0
ViRoguesAuthor Commented:
Could you provide me a code to check? As I said, I am new to this so the only thing I would be able to do is connect to the database.
0
Julian HansenCommented:
Look at the IGNORE option for INSERT. This saves you having to do the check first. You do an insert on the table regardless - make sure you have a Unique index setup for the email address thought.

If the address already exists the insert will have no effect - you then check if a record was updated or inserted with the PHP msql_affected_rows($result) - if the return value is 0 the email address exists.

Example

$email = $_POST('email'); //assume you will do more checking for security
... // get other fields here

$query = "insert IGNORE into users (... other fields, email) values (... other field values, $email)";
$result = mysql_query($query);
if ($result && mysql_affected_rows($result) == 1)
   echo "New user";
else
  echo "Email already exists";
0
VenabiliCommented:
Something like this (if I am reading your table structure properly and I had not done a stupid mistake)
$db = mysql_connect("*CHANGEDforDisplayPurposes*", "*CHANGEDforDisplayPurposes*", "*CHANGEDforDisplayPurposes*"); 
 mysql_select_db("*CHANGEDforDisplayPurposes*",$db); 
$sql = "Select * from personnel where email=".$values['email']));
$result = mysql_query(sql);
if (!$result){
   $errors['email'] = 'The email address is already in use.'; 
}

Open in new window

0
ViRoguesAuthor Commented:
@julianH

I don't really understand where I would place that.

@Venabili

I tried this code but I get the error even though there isn't an existing email address.
0
VenabiliCommented:
What kind of error?

You can use if ($returned_rows == 0)
instead of
if (!$result)
if you want - but if that query returns a result, it means you have the mail there

Can you post your VerifyForm method now?
0
ViRoguesAuthor Commented:
This what the code looks like now. When I type an email address that is not in the database, it still returns the error "The email address is already in use."
<?php

function VerifyForm(&$values, &$errors)
{

    
    if (strlen($values['first']) < 3)
        $errors['first'] = 'The name you provided is too short.';
    elseif (strlen($values['first']) > 50)
        $errors['first'] = 'The name you provided is too long.';

    if (strlen($values['last']) < 2)
        $errors['last'] = 'The name you provided is too short.';
    elseif (strlen($values['last']) > 50)
        $errors['last'] = 'The name you provided is too long.';

    if (strlen($values['nickname']) < 1)
        $errors['nickname'] = 'The name you provided is too short.';
    elseif (strlen($values['nickname']) > 50)
        $errors['nickname'] = 'The name you provided is too long.';
        
    if (!ereg('.*@.*\..{2,4}', $values['email']))
        $errors['email'] = 'The email address you provided is invalid.';
		
		
		$db = mysql_connect("*CHANGEDforDisplayPurposes*", "*CHANGEDforDisplayPurposes*", "*CHANGEDforDisplayPurposes*");
 mysql_select_db("*CHANGEDforDisplayPurposes*",$db);
 $sql = "SELECT * FROM personnel where email=".$values['email'];
$result = mysql_query(sql);
if (!$result){
   $errors['email'] = 'The email address is already in use.'; 
}
		
		
		require_once('recaptchalib.php');
$privatekey = "*CHANGEDforDisplayPurposes*";
$resp = recaptcha_check_answer ($privatekey,
                                $_SERVER["REMOTE_ADDR"],
                                $_POST["recaptcha_challenge_field"],
                                $_POST["recaptcha_response_field"]);

if (!$resp->is_valid) {
  $errors['captcha']="The reCAPTCHA wasn't entered correctly. Go back and try it again.";
}

        
    return (count($errors) == 0);
}

function DisplayForm($values, $errors)
{
    ?>

Open in new window

0
rjdownCommented:
You have a missing dollar sign in front of sql in line 29.

$result = mysql_query($sql);
0
VenabiliCommented:
yep - and looking up, I missed it initially(thus the remark for the stupdi mistake-  always do them)

Try with the correct syntax and let's see how this works.
0
ViRoguesAuthor Commented:
I tried it, with the correct syntax, but nothing seems to have changed.

It still displays the error (The email address is already in use.).
0
Julian HansenCommented:
You would modify Line 120 like so

 $sql = "INSERT IGNOORE INTO personnel (firstname, lastname, nick, email) VALUES ('$first','$last','$nickname','$email')";
      $result = mysql_query($sql);
if (mysql_affected_rows($result) == 1)
echo "Thank you! Information entered.";
else
echo "Email already exists";

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VenabiliCommented:
This is because you first insert and THEN call verify - which makes no sense - you want to check the things before importing them.

So you need to change the ProcessForm order a bit - first verification then insert.
0
ViRoguesAuthor Commented:
@julianH

Your solution works, but I still get this error:

"Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in /home/content/v/i/r/virogues/html/test/3valO.php on line 123
Email already exists"
0
rjdownCommented:
Change

if (mysql_affected_rows($result) == 1)

to

if (mysql_affected_rows() == 1)
0
ViRoguesAuthor Commented:
It works now. Thanks all.
0
ViRoguesAuthor Commented:
Thanks all for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.