[RESOLVED] verify unique emailaddress

[tcp]

I've been using this code for a couiple years now and problems are becoming apparent. Users register and this code snippet checks to see if the emailaddress already exists in the DB. Works sometimes but still getting duplicate addresses especially back to back registrations.

$check_email = mysql_query("SELECT emailaddress FROM members WHERE emailaddress='$emailaddress'");

	$Rows = mysql_num_rows($check_email); 

	// This line checks to see if emailaddress matches were found.

	if (!$Rows < 1) 

		{
		$error = " ";
		print ("<b>Error:</b> ");
		print (" The email address <b>");
		print ("<U>$emailaddress</U>");
		print ("</b> is already in use.<BR>");
		print ("<p>");
		$verify = "bad";
	}

[laserlight]

Declare a unique constraint for that column in the database. In PHP, attempt to insert and check for a unique constraint violation (i.e., mysql_query() will fail and you can check the error code). You do not need to check if such a value already exists for that column before attempting the insert.

[tcp]

I am using phpMyAdmin and the index table shows 'keyname' member_id is PRIMARY (and is assigned incrementally by mysql, btw) and 'keyname' emailaddress is unique. I set this database up a long time ago easily could have been pre mysql 3.2x. I recently upgraded to mysql 5.1.3

If I insert using phpMyAdmin I can duplicate an emailaddress. If I run ALTER TABLE members ADD UNIQUE (emailaddress) I immediately get an error showing one of the duplicate emailaddresses.

Is index unique different from ALTER TABLE 'members' ADD UNIQUE ('emailaddress')?

[laserlight]

tcp wrote:

If I insert using phpMyAdmin I can duplicate an emailaddress.

hmm... if there is already a unique constraint on that column, then this should be impossible, unless MySQL 5.1.3 is horribly broken.

tcp wrote:

If I run ALTER TABLE members ADD UNIQUE (emailaddress) I immediately get an error showing one of the duplicate emailaddresses.

You should remove all existing duplicates before doing this.

[tcp]

I ran this query to find duplicates.
<CODE>
SELECT emailaddress, count( emailaddress )
FROM members
GROUP BY emailaddress
HAVING count( emailaddress ) >1 </CODE>

and got 1436 results.

[halojoy]

It is important to realize
that what we see as equal is not necessarily equal.

Following submitted emailaddresses are one and the same
but still it can pass into a UNIQUE db field
and cause 3 duplicates.
The solution is this function [man]trim[/man]

<?php

//        As entered in POST form:
$email = '[e-mail]'
$email = '  [e-mail]'
$email = '  [e-mail]  '

// SPACE is a character with ASCII code 32
// So those 3 submissions, strings, are unique, not equal

// The solution when validate and get data from $_POST
$email = trim($_POST);  // Removes any leading and trailing SPACES

?>