[RESOLVED] checking email address isnt a duplicate (php and mysql)

[cainy1982]

Hi all I am a total newbie at this so please bear with me.

I have created some code and i want to make sure users dont register with duplicate email address, i have had a play but cant seem to get it to work.

<?php // This page will create a form that registers the users in the database

// get the databse settings file and estblish a connection.

require_once ('../mysqli_connect.php');

// set the page title variable $page_title

$page_title = 'User Registration Page';

// get the page header file from the includes directory

include ('includes/header.html');

// Check if the form has been submitted:
if (isset($_POST['submitted'])) {

$error = array(); // Initialize an error array.

// Check for a first name:
if (empty($_POST['first_name'])) {
	$error[] = 'You forgot to enter your first name.';
} else {
	$first_name = trim($_POST['first_name']);
}

// Check for a last name:
if (empty($_POST['last_name'])) {
	$error[] = 'You forgot to enter your last name.';
} else {
	$last_name = trim($_POST['last_name']);
}

// Check for an email address:
if (empty($_POST['email_address'])) {
	$error[] = 'You forgot to enter your email address.';
} else {
	$email_address = trim($_POST['email_address']);
}

// Check for a password and match against the confirmed password:
if (!empty($_POST['pass1'])) {
	if ($_POST['pass1'] != $_POST['pass2']) {
		$errors[] = 'Your password did not match the confirmed password.';
	} else {
		$password = trim($_POST['pass1']);
	}
} else {
	$errors[] = 'You forgot to enter your password.';
}

if (empty($errors)) { // If everything's OK.

// Register the user in the database...

require_once ('../mysqli_connect.php'); // Connect to the db.

	// Make the query:
	$q = "INSERT INTO user_details (first_name, last_name, email_address, password, registration_date) VALUES ('$first_name', '$last_name', '$email_address', SHA1('$password'), NOW() )";		
	$r = @mysqli_query ($dbc, $q); // Run the query.
	if ($r) { // If it ran OK.

		// Print a message:
		echo '<h1>Thank you!</h1>
	<p>You are now registered. In Chapter 11 you will actually be able to log in!</p><p><br /></p>';	

	} else { // If it did not run OK.

		// Public message:
		echo '<h1>System Error</h1>
		<p class="error">You could not be registered due to a system error. We apologize for any inconvenience.</p>'; 

		// Debugging message:
		echo '<p>' . mysqli_error($dbc) . '<br /><br />Query: ' . $q . '</p>';

	} // End of if ($r) IF.

	mysqli_close($dbc); // Close the database connection.

	// Include the footer and quit the script:
	include ('includes/footer.html'); 
	exit();

} else { // Report the errors.

	echo '<h1>Error!</h1>
	<p class="error">The following error(s) occurred:<br />';
	foreach ($errors as $msg) { // Print each error.
		echo " - $msg<br />\n";
	}
	echo '</p><p>Please try again.</p><p><br /></p>';

} // End of if (empty($errors)) IF.

} // End of the main Submit conditional.
?>
<h1>Register</h1>
<form action="register.php" method="post">
	<p>First Name: <input type="text" name="first_name" size="15" maxlength="20" value="<?php if (isset($_POST['first_name'])) echo $_POST['first_name']; ?>" /></p>
	<p>Last Name: <input type="text" name="last_name" size="15" maxlength="40" value="<?php if (isset($_POST['last_name'])) echo $_POST['last_name']; ?>" /></p>
	<p>Email Address: <input type="text" name="email_address" size="20" maxlength="80" value="<?php if (isset($_POST['email_address'])) echo $_POST['email_address']; ?>"  /> </p>
	<p>Password: <input type="password" name="pass1" size="10" maxlength="20" /></p>
	<p>Confirm Password: <input type="password" name="pass2" size="10" maxlength="20" /></p>
	<p><input type="submit" name="submit" value="Register" /></p>
	<input type="hidden" name="submitted" value="TRUE" />
</form>
<?php
include ('includes/footer.html');
?>

i was thinking about inserting some code like

// create the query against the databse checking for users mathcing the email address entered in the form.
$q = "SELECT user_id FROM user_details WHERE email_address='$email_address'";

//run the query or provide an error message as to why it wasnt run.

$r = @mysqli_query ($dbc, $q);

// if the number of rows returned = 0 then the email address has not been used and it available.

if (mysqli_num_rows($r) == 0) {

} else {

// number of rows retured == 1 then email address is in use.

if (mysqli_affected_rows($dbc) == 1)
	$error[] ='The email is already in use please try another email.';

}

Any help would be great thanks

[halojoy]

affected_rows is not used with SELECT
num_rows is correctly what you use to count rows from a SELECT

I would change your script #2 a little bit

// create the query against the database 
// checking for users mathcing the email address entered in the form.
$q = "SELECT user_id FROM user_details 
WHERE email_address='$email_address'";

//run the query or provide an error message as to why it wasnt run.
$r = @mysqli_query ($dbc, $q);
// if mysql_error display the Query+Error
if(mysql_error()) exit($q.'<br>'.mysql_error());

// if the number of rows returned = 0 then the email address has not been used and it available.
if (mysqli_num_rows($r) >= 1) { // already in use?
    $error[] ='The email is already in use please try another email.';
}else{
    //email is alright and no error
} 

[cainy1982]

Perfect thanks for speedy reply, once I become a genuis i will make sure return here and help some newbies!!

[laserlight]

cainy1982 wrote:

I have created some code and i want to make sure users dont register with duplicate email address

The usual way to handle this is to create a unique constraint on the email address column. When users register, you simply insert their details and then check for a unique constraint violation (i.e., the query will fail if insertion will cause a duplicate).

The advantage of this is that it avoids a race condition where the two insertions happen almost simultaneously, but it so happens that both the corresponding checks are performed before either of the insertion take place, thus resulting in a duplicate. Admittedly, this is rare for your situation, but it is still theoretically possible unless you use the method that I described.

Of course, this does not stop you from also checking for a duplicate using an AJAXy user friendly interface, in which case the query you have is fine.

[cainy1982]

Yes that makes sense, but the level I am at is very inexperienced so the code above worked great for me.

The next step will be live validation once I have get the basics or registering logging in, admin controls and uplaoding images.

Thanks again

[bradgrafelman]

cainy1982;10943505 wrote:

Yes that makes sense, but the level I am at is very inexperienced so the code above worked great for me.

Why learn something the wrong way? Besides, using laserlight's method is not only more efficient, but it's easier too. Instead of executing a separate SELECT query, you just do the INSERT query and usual and do something like:

if(!$query && mysql_errno() == 1062) { // 1062 = duplicate value for key
    // insert code for invalid email address error here
}