...

View Full Version : How to check if an email address already exists in the database?



skcin7
01-17-2010, 08:41 AM
Hi. I am designing a user registration page. I allow only 1 membership per email address, so when a new member is signing up, if they provide an email address that is already in the database, it will give them an error. I already have everything set up, I just can't for the life of me figure out what to put in the function.

I messed around for a while and couldn't figure it out. I already have the function set up. Currently it just looks like this but obviously it's a stub for now:


function isEmailAlreadyInDatabase($email) {
//This is a stub
if(email has been found)
{
return true;
}
else
{
return false;
}

}

Thanks in advance for all help!!

Len Whistler
01-17-2010, 09:06 AM
Maybe something like this:






$new_email= $_POST['email'];

$sql = mysql_query("SELECT email FROM member_list WHERE email = $new_email");
$row = mysql_fetch_row($sql);

if(!isset($row[email])){
// no match .... continue.
} else {
// match , back to registration page.
}


Might be syntax errors.



------

kbluhm
01-17-2010, 10:31 AM
function isEmailAlreadyInDatabase( $email )
{
$res = mysql_query( 'SELECT `id` FROM `user` WHERE `user_email` = \'' . mysql_real_escape_string( $email ) . '\' LIMIT 1' );
return ( bool ) mysql_num_rows( $res );
}

skcin7
01-17-2010, 12:01 PM
Hi, thanks for your posts to help me. I tried both of them but they didn't work. Based on the posts in this thread, I wrote the following code which STILL doesn't work! Does anybody know why this doesn't work?


function isEmailAlreadyInDatabase($email) {

$sql = mysql_query("SELECT * FROM LUM_User WHERE Email = $email");

if($sql)
{
return true;
}
else
{
return false;
}

}

A few notes:
- The table is called LUM_User
- The field is called Email (capitalized)

I'm a novice at this and thanks for your help!

oesxyl
01-17-2010, 12:58 PM
Hi, thanks for your posts to help me. I tried both of them but they didn't work. Based on the posts in this thread, I wrote the following code which STILL doesn't work! Does anybody know why this doesn't work?


function isEmailAlreadyInDatabase($email) {

$sql = mysql_query("SELECT * FROM LUM_User WHERE Email = $email");

if($sql)
{
return true;
}
else
{
return false;
}

}

A few notes:
- The table is called LUM_User
- The field is called Email (capitalized)

I'm a novice at this and thanks for your help!


function isEmailAlreadyInDatabase($email) {
$query = "SELECT * FROM LUM_User WHERE Email = '".$email."'";
print $query; // check if query is valid and comment if is
$sql = mysql_query($query);
if($sql && mysql_num_rows($sql) == 1){
return true; // there is a single mail
}
return false; // invalid query, no mail address or more then one
}


best regards

skcin7
01-17-2010, 03:37 PM
These are still not working! :confused: What the hell am I doing wrong?

EDIT: I got it working! ALthough, I feel as if I am not doing it in the most efficient method. I basically use a while loop to loop through each row, and I check to see if the email is the same on each row, like this:


function isEmailAlreadyInDatabase($email) {

//Include connection settings
include 'includes/settings.php';
//Select database
mysql_select_db("nichol1_admin", $con);


$query = "SELECT * FROM LUM_User";
$sql = mysql_query($query);

while ($row = mysql_fetch_array($sql))
{
$email1 = $row['Email'];

if($email1 == $email)
{
return true;
}

}

return false;
}

bdl
01-17-2010, 04:55 PM
IMHO, the best way to do this is to enforce a UNIQUE constraint on the field (which you should be doing regardless) and then go ahead and perform a blind INSERT statement. If the INSERT fails due to the constraint, then ask the user to input a different email (or block them altogether, whatever logic you want to enforce). Check against the MySQL error #1062 using mysql_error().

The next best step is to fetch a COUNT of any matching record that matches the input value and then return the count value. If the count value is 1, the email exists and you can enforce logic on that case (of course if the count value is greater than 1 you have a bigger problem).

Retrieving any (other) data, certainly retrieving all data and looping through it until you find a match is very inefficient.

kbluhm
01-17-2010, 05:25 PM
Hi, thanks for your posts to help me. I tried both of them but they didn't work. Based on the posts in this thread, I wrote the following code which STILL doesn't work! Does anybody know why this doesn't work?

That surprises me. I have used a set of similar calls with that type of query in the past with no issues.

Another set of calls I have used with success would be similar to:


function isEmailAlreadyInDatabase( $email )
{
$res = mysql_query( 'SELECT COUNT( `id` ) AS `count` FROM `user` WHERE `user_email` = \'' . mysql_real_escape_string( $email ) . '\' LIMIT 1' );
$row = mysql_fetch_assoc( $res );
return ( bool ) ( int ) $row['count'];
}

masterofollies
01-17-2010, 05:57 PM
There is an easier way of doing it, quick and simple. But remember you have to change these examples to match yours. Meaning change the table name, field name, etc to your database.

bdl
01-17-2010, 05:58 PM
@kluhm> Both of your code segments offer a better way than most; however, you're returning a value cast as a BOOL, which may not give the results you expect, based on whether or not the actual return value can be neatly processed as a boolean.

kbluhm
01-17-2010, 06:27 PM
Casting it to boolean doesn't [shouldn't] affect the desired result. If the query's returned value is 0, it will be cast as FALSE, reflecting that the email does not currently exist. Any other numeric value is cast to TRUE, meaning it exists.

JAY6390
01-17-2010, 06:38 PM
I notice that none of the code except the OP's "inefficient" way of doing it has no mysql_select_db() in them, where as that one does. Do you actually do that outside of the function when you connect to the database? You should be doing both at the start of your script really

EDIT: and also the connection setting - Is it that you've not connected even before this point?

MattF
01-17-2010, 08:23 PM
function isEmailAlreadyInDatabase($email)
{
//Include connection settings
include 'includes/settings.php';
//Select database
mysql_select_db("[insert the correct name here]", $con);

$sql = mysql_query('SELECT 1 FROM member_list WHERE LOWER(email)=\''.mysql_real_escape_string(strtolower($email)).'\'');

return ((mysql_num_rows($sql)) ? true : false);
}

skcin7
01-18-2010, 04:45 PM
I notice that none of the code except the OP's "inefficient" way of doing it has no mysql_select_db() in them, where as that one does. Do you actually do that outside of the function when you connect to the database? You should be doing both at the start of your script really

EDIT: and also the connection setting - Is it that you've not connected even before this point?

I think the reason why it wasn't working at first is because it didn't have mysql_select_db() in it. For some reason it will only work if I add the connection settings and select the database INSIDE the function, even though I already have this code outside of the function at the top of the page.

skcin7
01-18-2010, 04:48 PM
IMHO, the best way to do this is to enforce a UNIQUE constraint on the field (which you should be doing regardless) and then go ahead and perform a blind INSERT statement. If the INSERT fails due to the constraint, then ask the user to input a different email (or block them altogether, whatever logic you want to enforce). Check against the MySQL error #1062 using mysql_error().

I think based on what you described this sounds like a very good way of doing it. How do you enforce a UNIQUE constraint on a field? I'm using phpMyAdmin to handle the database but I can't seem to find a way to add UNIQUE to a field.

EDIT: I figured out to alter the table and make a field unique you need to execute some code. So, I executed this SQL:


ALTER TABLE `LUM_User` ADD UNIQUE (
`Email`
)

I got a message saying that the SQL statement executed, so the Email field is now has the UNIQUE constraint applied right? I can't seem to find in phpMyAdmin any indication of this.



Retrieving any (other) data, certainly retrieving all data and looping through it until you find a match is very inefficient.

I agree, the way I have it set up now is very inefficient :-( (although at least for the time being it works)

skcin7
01-18-2010, 04:52 PM
That surprises me. I have used a set of similar calls with that type of query in the past with no issues.

Hey. I think it wasn't working because I didn't include the connection settings or select the database inside the function. For some reason that made it not work, although I did do this outside of the function (strange).

shadowmaniac
01-18-2010, 05:01 PM
I got a message saying that the SQL statement executed, so the Email field is now has UNIQUE applied right? I can't seem to find in phpMyAdmin any indication that it's UNIQUE (although I think it is).
To set a field as UNIQUE in phpmyadmin, go to phpmyadmin, select your database.
You should now see a list of all tables within that DB.
Under action, choose structure for the table that you wish to modify.
You will now see a list of all fields within the selected table. To the far right is yet another set of action; under that, there's an icon with a U. Click on that icon to set that specific field as UNIQUE.


Also, no need to triple post... simply edit your last post.

skcin7
01-19-2010, 02:52 AM
This problem is resolved. Many thanks to all of you! For the record, this is what my final function looks like:

function isEmailInUse($email)
{
// Include connection settings and select database
include 'includes/settings.php';
mysql_select_db("nichol1_admin", $con);

$res = mysql_query( 'SELECT `UserID` FROM `LUM_User` WHERE `Email` = \'' . mysql_real_escape_string( $email ) . '\' LIMIT 1' );
return ( bool ) mysql_num_rows( $res );
}

Especially thanks to: kbluhm, because this is pretty much the same code that you suggested, though it wasn't working properly at first because I didn't have the connection settings inside the function, and also thanks to bdl because I didn't have the knowledge to make a UNIQUE constraint in my database before he mentioned it, and it's definitely a good idea to do that. Thanks!

buggy
01-19-2010, 03:53 AM
I use this for username check but I guess you can edit it to work for emails to ...

// checks if the username is in use
if (!get_magic_quotes_gpc()) {
$_POST['username'] = addslashes($_POST['username']);
}
$usercheck = $_POST['username'];
$check = mysql_query("SELECT username FROM users WHERE username = '$usercheck'")
or die(mysql_error());
$check2 = mysql_num_rows($check);

// if the name exists it gives an error
if ($check2 != 0) {
die('Sorry, the username '.$_POST['username'].' is already in use.');
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum