E-mail address already in database?

[nasio]

Hello,

I have a table "Members" with the following fields: id, email, name. Before a new user sign up I want to check and see if their e-mail address is already in the database. I've created the following code:

$result = mysql_query("SELECT * FROM Members WHERE email='$email'");
if ($result) {
echo("<P>E-mail address already in database!");
exit();
}

But this code is not correct. The variable $result is true even if it doesn't find the e-mail address in the database (it took me a while to figure this out). How do I check if the e-mail address is already in the database? Please, the easy way!

[petesmc]

Try:

$result = mysql_query("SELECT * FROM Members WHERE email='$email'");
$num = mysql_numrows();
if ($num == "1") {
echo("<P>E-mail address already in database!");
exit();
}

[DarkMonkey]

or the way I do it which is much the same, but slightly neater

$result = mysql_query("SELECT * FROM Members WHERE email='$email'"); 
if (mysql_num_rows($result)==1) { 
echo("<P>E-mail address already in database!"); 
exit(); 
} 

[petesmc]

You should use mysql_numrows as it is compatable with PHP3. It may never be neccessary but I tend to use it because my scripts are used by people with PHP3.

I don't think there is any speed difference at all, so....

That's all
-Peter

[DarkMonkey]

Yeah, I guess if your making scripts for general use, or download then I guess you might want to worry about compatibility, one of the things I love most about server side programing though is NOT having to worry about that stuff. If the script is just for your own use/company/client, you should probably code for php4 unless you know they dont have that installed... they should though

[nasio]

Thanks for the help guys! Another question about this:
I use the following code to create the table

mysql> CREATE TABLE Members ( 
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
-> email VARCHAR(100) NOT NULL, 
-> name VARCHAR(100) NOT NULL
-> );

Like I said, I check to see if an e-mail address already exist in the database. But I want to make it impossible to add the same e-mail address even if my check fails! In other words I want to make the email field unique. How do I do this? And those it make sense to do this or should I stick with the e-mail check? Please advice.

[hgould]

You should look at the structure of your database, the rules for relational data integrity require the data in a primary key field to be unique.

It follows then, that a simple fix to your "one email address ever" problem would be, unless you have a good reason not to, is to get rid of your current autoincrement PK and use the email address as the primary key in this table.

mysql> CREATE TABLE Members (
-> email VARCHAR(100) NOT NULL PRIMARY KEY,
-> name VARCHAR(100) NOT NULL
-> );

Hope this helps

[DarkMonkey]

It is my opinion that *all* tables should have a unique id really, it makes table to table design so much easier and more flexible. Isn't there a 'unique' key you can add to fields that should be unique? Which can be used more than once, unlike primary key?

[nasio]

It is my opinion that *all* tables should have a unique id really, it makes table to table design so much easier and more flexible.

Yeah, I once posted a question about this on this board and I got a response from Kevin Yank himself (who I consider to be a PHP & MySQL expert) saying that all tables should have a unique id (int not null auto_increment primary key) field. Since then I always create the id field even if I never use it.

Lets say I want to create a table in the future that contains members submissions (like jokes, uploaded files, or whatever), then I could use this id in the second table to identify who submitted what! This second table should also have its own unique id (int not null auto_increment primary key) field.

[numeropi]

$result = mysql_query("SELECT * FROM Members WHERE email='$email'"); 
if (mysql_num_rows($result)==1) { 
echo("<P>E-mail address already in database!"); 
exit(); 
} 

I'd make

if (mysql_num_rows($result)==1) 

be

if (mysql_num_rows($result)>0) 

Why? because if, by the time you implement this scheme, your database already contains duplicate e-mails, or you add a bunch of e-mails automatically (importing data) without using the script, you can still guarantee that no more duplicates can be added.

Actually, I think this corresponds more accurately to the concept you are trying to code: you are not interested in knowing if there is exactly one entry in the table, you want to know if there's *any* entry in the table with that e-mail.

If you're writing, let's say, a subscription system, using this system you can guarantee that even if the database already contains a duplicate of my address because of some mistake, I won't be able to sign up again.

Also, if you ever intend to port your code to a database engine other than MySQL, you should check the case of the e-mail address in the search. The way I do that is to do

$email=strtolower($email); 

before adding the row.

I use the following code to create the table

create table members (email VARCHAR(100) NOT NULL,
-> name VARCHAR(100) NOT NULL
-> );

Like I said, I check to see if an e-mail address already exist in the database. But I want to make it impossible to add the same e-mail address even if my check fails! In other words I want to make the email field unique. How do I do this?

Create your table with something like

create table members(memberid integer auto_increment primary key,
-> email varchar(100) not null unique,
-> name varchar(100) not null
-> );

Using that, you no longer need to perform any prior check by yourself, MySQL won't let you insert a duplicate e-mail, and you will have to check the $result to see if the insert was successful or not. However, beware, because MySQL does all comparisons case insensitively, which is right for you, but other RDBMS will take case into account, so me@myhost.com will not be thought of as a duplicate of Me@MyHost.com, and you will have a problem.

Hope you find that useful.

[nasio]

create table members(memberid integer auto_increment primary key,
-> email varchar(100) not null unique,
-> name varchar(100) not null
-> );

Using that, you no longer need to perform any prior check by yourself, MySQL won't let you insert a duplicate e-mail, and you will have to check the $result to see if the insert was successful or not.

Yeah, I understand. But if $result returns an error, how do I know if it's because the e-mail address is already in the database, or because the database is currently not available, or something else? How would I know?

[Jppr]

you can use the php function mysql_error() to display the errors the mysql daemon outputs.

[nasio]

you can use the php function mysql_error() to display the errors the mysql daemon outputs.

No, I want to display my own "user friendly" error messages. With "user friendly" I mean telling the user what's wrong and how to correct the problem. Like in the case of duplicate e-mail address you can display something like:

"The e-mail address you've entered is already in our database. If you've already registered but lost your password........"

You wont get this with the mysql_error() function!

[Jppr]

the duplicate e-mail check using php in the beginning of this post is quite good and water-resistant, so the additional security with mysql would only mess up your error messages. What's wrong with the way you started?

[numeropi]

No, I want to display my own "user friendly" error messages.

Use mysql_errno(). The error code for a duplicate entry in a unique column is 1062. You can find all the error codes in mysqld_error.h in the source distribution.