asked on

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?

Venabili

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?

ViRogues

ASKER

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.

Venabili

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.

ViRogues

ASKER

How would I do that? Sorry, I'm new to PHP.

Venabili

Depends on how you send the mails. Can you show your code?

ViRogues

ASKER

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

Venabili

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.

ViRogues

ASKER

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.

Julian Hansen

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";

SOLUTION

Venabili

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ViRogues

ASKER

@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.

Venabili

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?

ViRogues

ASKER

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

rjdown

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

$result = mysql_query($sql);

Venabili

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.

ViRogues

ASKER

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.).

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Venabili

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.

ViRogues

ASKER

@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"

SOLUTION

rjdown

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ViRogues

ASKER

It works now. Thanks all.

ViRogues

ASKER

Thanks all for the help.