SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  2. #2
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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();
    }

  3. #3
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    or the way I do it which is much the same, but slightly neater

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


  4. #4
    <? echo "Kick me"; ?> petesmc's Avatar
    Join Date
    Nov 2000
    Location
    Hong Kong
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help guys! Another question about this:
    I use the following code to create the table

    Code:
    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.

  7. #7
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Dunedin, NZ
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by nasio; Apr 29, 2001 at 18:41.

  10. #10
    SitePoint Enthusiast numeropi's Avatar
    Join Date
    Apr 2001
    Location
    somewhere between 3.14 and 3.15
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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
    PHP Code:
    if (mysql_num_rows($result)==1
    be
    PHP Code:
    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

    PHP Code:
    $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

    Code:
    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.

  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Code:
    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?

  12. #12
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use the php function mysql_error() to display the errors the mysql daemon outputs.

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  14. #14
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  15. #15
    SitePoint Enthusiast numeropi's Avatar
    Join Date
    Apr 2001
    Location
    somewhere between 3.14 and 3.15
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •