Hi guys, i'm having a problem trying to look up whether an email address has already been added to my newsletter database... it seems really simple to do, but i just can't seem to get it to work...

so the logic goes like this:
- user inputs email address in signup form on front page
- email address is passed to the confirm page
- we check to see if that email address has already been added to the database, by doing a $sql select statement
- if the address has already been added, we output an error message telling the user to use another address..
- if not, we add the address to the database using a $sql insert query...

and this is the code i'm trying to do it with: ;-)
(i'll modularize it when i get it all to work properly)

 
<?php
$email = $_POST['email'];
/* declare some relevant variables */
$hostname = "******";
$username = "******";
$password = "******";
$dbName = "******";

/* MySQL table created to store the data */
$userstable = "newsletter";

/* make connection to database */
MYSQL_CONNECT($hostname,$username,$password) OR DIE("Unable to connect to database");
@mysql_select_db("$dbName") or die("Unable to select database"); 


// Check for existing email address
// is this query below the best to use????
    $sql = "SELECT COUNT (*) FROM $userstable WHERE email = $email'";

$result = mysql_query($sql);
if (!$result) { 
error ("A database error occurred in processing your".
        "submission.\\nIf this error persists, please".
        "contact [email]webmaster@me.com[/email]." );
}
if (mysql_result($result,0,0)>0) {
   error("A user already exists with your chosen email.\\n".
          "Please try another." );
}
 else {
 /* Insert information into table */
/// i'm not sure if this is the right place to put this???

 $query = "INSERT INTO $userstable SET email='$email'";
 $result = MYSQL_QUERY($query);
 }

/* Close the database connection */
MYSQL_CLOSE();
?>

Would appreciate any comments or ideas or criticisms as i've been at this for 2 days now, and i can get the emails to be inputted into the database fine, and can prevent duplicates by making the email column in the database unique
(prevents duplicates from being added, but i dunno how to output a sql eror message telling the user so...
- does anyone know how to do this???)
but i'm really battling with this seemingly basic concept, or more to the point, putting it into workeable code:

  • check if the email address exists in the column
  • if it does, say so, and tell the user
  • if it doesn't, add it to the database...

Anyway, i wanna go sleep now

thanks in advance guys and gals ;-)

gareth

    Why don't you try this:

    
$result = mysql_query($sql);
    if ($result) 
    {    
    
        if (mysql_result($result,0,0)>0) 
       {
         error("A user already exists with your chosen email.\n".
              "Please try another." );
       }
      else 
      {
             /* Insert information into table */
            $query = "INSERT INTO $userstable SET email='$email'";
            $result = MYSQL_QUERY($query);
      }
}

      hi guys

      here's the final code that works for me...

      // Check for existing user with the new email address 
$sql = "SELECT COUNT(*) FROM $userstable WHERE email = '$email'"; 
$result = mysql_query($sql);
  if ($result)
  {    
      
   if (mysql_result($result,0,0)>0) 
      {
    echo ("A user already exists with email address:<br><strong>$email</strong><br><br>Please try another." );
      }
     else
     {
    /* Insert email address into table */
    $query = "INSERT INTO $userstable SET email='$email'";
    $result = MYSQL_QUERY($query);
     }
  }

      any comments or suggestions would be appreciated!!

      regards
      gareth

        Write a Reply...