Making sure email isn't inserted into the database if already there

[gwh]

Hi everyone,

I have a basic newsletter signup form on the home page of a website with the following code:

<form name="form1" action="<?php echo $editFormAction; ?>" method="POST" onsubmit="MM_validateForm('Name','','R','Email','','RisEmail');return document.MM_returnValue">
  <div>					      <label for="Name">Name</label>
    <input class="text" id="Name" name="Name" type="text" />
  </div>						  

<div>				  <label for="Email">Email</label>
  <input class="text" id="Email" name="Email" type="text" />
  <input name="SubSubscribed" type="hidden" id="SubSubscribed" value="1" />
  </div>		   

 <div class="submit">
   <input class="submit" name="Subscribe" type="submit" value="Subscribe" />
   </div>
	                  <input type="hidden" name="MM_insert" value="form1" />
	          </form>

I have a 4-column table set up in phpMyAdmin with the following headings:

ID
SubName
SubEmail
SubSubscribed

And I also have the following php on the page, which inserts the name and email into the table when the subscribe button is pressed. The SubSubscribed field is a hidden field with a value of 1 and it populates the identically named field in the database to indicate a subscription has been authorized.

<?php require_once('connections/connSubscribe.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO subs (SubName, SubEmail, SubSubscribed) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['Name'], "text"),
                       GetSQLValueString($_POST['Email'], "text"),
                       GetSQLValueString($_POST['SubSubscribed'], "int"));

  mysql_select_db($database_connSubscribe, $connSubscribe);
  $Result1 = mysql_query($insertSQL, $connSubscribe) or die(mysql_error());

  $insertGoTo = "confirm_subscribe.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>

Everything works as it should except that it's allowing a name or email that already exists in the database to be inserted again.

I wondered if someone could tell me how to revise the code so that it won't allow this duplication to happen?

Appreciate any help offered.

[NogDog]

Assign a unique index to the email column. Then when you do your insert query, get rid of the "or die()" portion of your query command. Instead, check to see if $Result is false. If it is, check to see if mysql_errno() is 1062. If it is, then it is a duplicate entry, so alert the user and ask them to resubmit, or whatever it is you want to do in this case. If it's not 1062, then it's some other database query error that you want to log and return some sort of "Sorry, please try again..." message to the user.

[gwh]

Thanks for the reply,

I understand about assigning the unique key, and if there is a duplicate entry then I just need the alert to say that their email address is already in the database. I'm still a bit confused on how to revise the code as you suggest. I assume it's the following lines that need revising:

mysql_select_db($database_connSubscribe, $connSubscribe);
$Result1 = mysql_query($insertSQL, $connSubscribe) or die(mysql_error());

Can you help me a bit further?

[NogDog]

$Result1 = mysql_query($insertSQL, $connSubscribe);
if($Result1 == false)
{
   if(mysql_errno() == 1062
   {
      // handle duplicate key error here
   }
   else
   {
      user_error(mysql_error());
      // some error message to user here
   }
}
else
{
   // do processing if query was successful
}

[gwh]

Great - thanks very much