Checking An E-mail Address Against A Database

[sparkydude03]

Hi all! I want to check an e-mail address submitted via form, against a database of e-mail addresses. How would I do this? This is what I've got so far...

function checkDuplicates($email)
	{
	$db_email = "";

@mysql_connect($strhost,$struser,$strpass)
	or die("Could not conenct to database.");

@mysql_select_db($strdb)
	or die("Could not select $strdb.");

$query = "select * from giveaway_tb
where email = '$email'";

	if(mysql_query($query))
		{
		$db_email = mysql_query($query);

		echo "Sorry the email address: $email has already entered the contest.";
		}
	else {
	}
}

[benracer]

Try this

host=""; // Host name 
$username=""; // Mysql username 
$password=""; // Mysql password 
$db_name=""; // Database name 
$tbl_name=""; // Table name 

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("Sorry - We could not connect you to the online shop<br>Please try again later");

// username and password sent from signup form 
$email=$_POST['email']; 

$email = mysql_real_escape_string($email);

$sql="SELECT * FROM $tbl_name WHERE email='$email'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

if($count==1){
echo "They Match";
}else {
echo "error";
}

[nemonoman]

or in one long SQL

if(mysql_result(mysql_query("SELECT count(*) FROM table WHERE email='$email'")))>0){
  echo "Existing email";
}

[bradgrafelman]

Or, if you're planning on storing the e-mail address if it has not been entered already, throw a UNIQUE index onto the email column and don't even bother with a SELECT query at all...

EDIT: Also, nemonoman's code will always print "Existing email" unless your query becomes syntactically incorrect (e.g. you don't escape the data).

Speaking of... your code is vulnerable to SQL injection attacks; user-supplied data should never be placed directly into a SQL query! Instead, escape it with a function such as [man]mysql_real_escape_string/man.

[bogu]

You should also use String Comparison Functions (LIKE, NOT LIKE, SOUNDS LIKE(v4.1.0)) insted of "=" comparison operator ...

[bradgrafelman]

Eh, I don't see any reason to use LIKE/etc. ... a simple comparison is all that's needed.

[piersk]

I agree. LIKE will just unnecessarily slow down your query. If you're checking to see if the email address has already been entered (say if you're using it as a username for example) then if there's an email addy that contains the same text as yours, it doesn't matter, as long as it doesn't match.

i.e. say I enter [e-mail] (an old one of mine). I don't care if someone comes along and enters [e-mail] (if such a domain exists)

[bogu]

piersk wrote:

I agree. LIKE will just unnecessarily slow down your query.

Why do you say that?

I made a test on a table with 5 mil. records, this is the results I get ...

mysql> SELECT COUNT(*) FROM `table` WHERE field = 'string';
+----------+
| COUNT(*) |
+----------+
|   953924 |
+----------+
1 row in set (14.81 sec)
------------------------------------------------------------------------------
mysql> SELECT COUNT(*) FROM `table` WHERE field LIKE 'string';
+----------+
| COUNT(*) |
+----------+
|   953924 |
+----------+
1 row in set (13.22 sec)

Aprox. 1 sec. speed difference ...

[bradgrafelman]

Did you run those queries several (e.g. more than 10 at least) and average the results to see which was faster? It can be hard to get accurate benchmarks on a desktop computer, so a one-time execution of each doesn't give very reliable data.

[rulian]

field LIKE 'string' is the same as field = 'string'
because it's not doing any comparison

field LIKe '%string%'
should take quite a bit longer

[bogu]

bradgrafelman wrote:

Did you run those queries several (e.g. more than 10 at least) and average the results to see which was faster?

Yeap I did more then one times and each time LIKE was faster, the one that I posted was the last executed query, but there was some closer results so you are probably right ...