I have a mysql database that includes the fields first_name, last_name, email.

I have an input form to enter in the above information. If the e-mail address already exists, then I have a message page that lets the user know this and display the first and last name that already exists. Duplicate first and last names are OK.
The code below is not catching the duplicate e-mail address that is entered in the input form. How do I get it to work?

$result = mysql_query("SELECT * FROM mail_list_db where '$email_address' = email")  or die(mysql_error());
$count = mysql_num_rows($result);

echo "Count = " . $count . " " . $FName . " " . $LName; //<== Always display zero instead of one if the e-mail address already exists. First and last names display correctly.

if($count == 0) {
    // save the information to the database
    $query="INSERT INTO mail_list_db (first_name, last_name, email, id_key, remove, date_added, notes)
        VALUES('$FName', '$LName', '$email_address', '$id', '$remove', '$time_now', '$notes')";
    mysql_query($query) or die ("Error in query: $query");

// Now go back to the input form
print "<meta http-equiv=\"refresh\" content=\"0;URL=add.php\">";
}
else {
    // Display duplicate message along with first nad last name of the DB record.
}

If I change the first line to use either the first or last name, the count is greater than zero if the first or last name already exists.
Example:
$result = mysql_query("SELECT * FROM mail_list_db where '$LName' = last_name") or die(mysql_error());

The new records are being saved and I have tried it with many different e-mail addresses.

Personally: if you make the e-mail column unique in your table, then you can just insert your new values. If the e-mail already exists, the insert query will fail.

Also, try to trim $email_address value, if there is a space at the beginning or at the end you get an empty result set:

$email_address = trim($_POST['email_address']);

Its a good one to catching the Email Ids, its really useful one, thanks...

pritaeas, I tried testing making the email field unique, but I would like to get the first name and last name that already has this email address and display it so the person entering the names will know who has it.

After much testing I am sure it has to do with the "@" symbol in the email address. When I do a test just removing the "@" character the count equals one, which is correct. I tried using "LIKE" instead of "=" but that did not work. I tried $email = mysql_real_escape_string($email); but that did not help. I tried putting percent signs around the variable in the select statement, SELECT email FROM mailing where email = "%$email%", but that did not help either. Any ideas?

cereal, I did your trim and that did not help. Thanks for the idea.

I would like to get the first name and last name that already has this email address and display it so the person entering the names will know who has it

That sounds like a privacy issue to me...

Anyway, you didn't use mysql_fetch_array after the mysql_query so am not sure if that's all code, but it doesn't look complete.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.