Form Validation and Check for Existing Email

[akrohn]

I have made a form that asks a user for email, first name, last name, and password. I am using Spry validation tools in Dreamweaver. When I used those only, I did not have a problem. The problem began once I tried to actually check to see if the email was already registered. I have tried changing so many things like what $_POST variable to look for at the beginning, to different db connection arrangements. I am stumped. The only other thing I can think of is that I have the order wrong somehow in the logic. Thanks for the help.

 

First, here is the form:

 

Enter Email<?php if($error_email_taken) echo ": $error_email_taken."; ?>
<form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<input name="email" type="text" id="email" value="<?php if($_POST["email"]) echo $_POST["email"]; ?>">
<input name="first" type="text" id="first" value="<?php if($_POST["first"]) echo $_POST["first"]; ?>">
<input name="last" type="text" id="last" value="<?php if($_POST["last"]) echo $_POST["last"]; ?>">
<input name="pass" type="password"  class="formText1" id="pass" value="<?php if($_POST["pass"]) echo $_POST["pass"]; ?>">
<input type="submit" name="Submit" value="Submit"></td>
</form>

 

And the email verification and insert, which is placed before the opening html tag.

 

<?php
if($_POST['Submit'])
{	//Check to see if email is registered.
	$email = $_POST['email'];

	$dbc=mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
	$q_1 = "SELECT users_email FROM table WHERE users_email = $email";
		$r_1 = mysqli_query($dbc, $q_1);
		$rows_1 = mysqli_num_rows($r_1);
		if ($rows_1 == 0) 
		{ //If 0, email is not already registered.
			$new_email = $_POST['email'];
		} else {
			$error_email_taken = "This email is already registered.";
		}

//If everything is good, insert the information.
if(isset($new_email))
	{		
	$first_name = $_POST['first'];
	$last_name = $_POST['last'];
	$password = $_POST['pass'];

	//Insert User information.
	$q_2 = "INSERT INTO table (users_email, users_first, users_last, users_pass) 
		 VALUES ('$new_email', '$first_name', '$last_name', '$password')";
                $r_2 = mysqli_query($dbc, $q_2);

	//Go to new page if form was submitted and information properly inserted.
	header('Location: new_page.php');
}//End: if($new_email)	
} //End: if $_POST['submit']
?>

 

I've simplified it as much as I could. I totally eliminated stuff like a password hash, etc. because I wanted to get it down to the most simple form, so once this gets working, I'll add that other stuff later. Thanks so much again.

[Pikachu2000]

You haven't told us what "the problem" actually is, including any errors, etc. you're getting, and what you've already tried to debug it yourself.

[xyph]

You assume your MySQL query is not failing.

 

You need to check for errors, and develop with error reporting turned on. See signature.

[akrohn]

OK. I have worked some of the issues out. Here is my new email validation code. It now will insert the information into the database, and will redirect to the new page. But it seems to not check against existing emails in the database. Here it is:

 

<?php
if($_POST['Submit'])
{	//check to see email was submitted. If yes, check to see if email is registered.
$username="username";
$password="password";
$database="database";

$dbc=mysql_connect ("localhost", $username, $password) or die ('Could not connect:');
mysql_select_db ("$database");

	$email_check = $_POST['email'];

	$q_1 = "SELECT * FROM table WHERE users_email = $email_check";
	$r_1 = mysql_query($q_1);
	$rows_1 = mysql_num_rows($r_1);
		if ($rows_1 >= 1) //If >= 1, email is already registered. Create error.
		{ 
			$error_email_taken = "This email is already registered.";
		} else { //assign email variable
			$e = $_POST['email'];
		}

//If everything is good, insert the information.
if(isset($e))
	{		
	$fn = $_POST['first'];
	$ln = $_POST['last'];
	$p = $_POST['pass'];

	//Insert User information.
	$q_2 = "INSERT INTO table (user_email, user_first, user_last, user_password) 
		 VALUES ('$e', '$fn', '$ln', '$p')";
	mysql_query($q_2);
	//close the connection
	mysql_close();
	//Go to registration submitted page.
	header('Location: http://www.newpage.php');
}//End: if($e)	
} //End: if $_POST['submit']
?>

 

So what this does now is it will insert the information into the database, regardless of an existing email, and then it does redirect to the new page. But I think it has to be running through that first query and checking for an existing email, because it seems that it must be assigning the variable $e to $_POST['email'], because the next thing is to check for that varible ($e) in order to execute the INSERT, and the INSERT is happening. So I am not receiving any errors, but really, I'm not sure how to write that into the code so that it stops where the error may be occuring.

 

Also, when I assign the variable $error_email_taken, does that make sense to anyone else how I want that to work, where if there is an existing email in the database, the variable is created then the script does not continue, and the form is shown again, with that variable showing the error in the form? Maybe one of my mistakes is that it can't work that way.

 

Thanks.

[redsmurph]

You set $error_mail_taken, but wat do you do then? You have to test for it and take action, not continue on to make an insert if the e-mail address is already in the database.

[xyph]

Again, you aren't checking for ERRORS.

 

I assure you, I understand why your script isn't working. I can't help you if you ignore my advice, though.

[akrohn]

xyph, could you help me understand how to apply the error checking? I really don't know where to begin with that.

[akrohn]

Ok. I have applied the error checking that you suggested. The error after form is submitted is:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource.

 

This is in the line of the first query: $rows_1 = mysql_num_rows($r_1);

[xyph]

If a MySQL query fails, it will return false. You need to check if it's returned false using a conditional (if) statement.

[litebearer]

also change this...

$q_1 = "SELECT * FROM table WHERE users_email = $email_check";

to this

$q_1 = "SELECT * FROM table WHERE users_email = '$email_check'";

[akrohn]

Isn't the conditional part what I am doing with this:

 

		if ($rows_1 >= 1) //If >= 1, email is already registered. Create error.
		{ 
			$error_email_taken = "This email is already registered.";
		} else {
			$e = $_POST['email'];
		}

[redsmurph]

I read your code again, and it looks correct that $e is only set if the else section has fired and the insert is then only done if $e is set, but I suspect that mysql_query returns false. You have to test for that before calling mysql_num_rows, otherwise $e will be set even for an error.

 

A remaining error is that you don't escape the strings in the insert. If an apostroph is in any of the strings you'll get an SQL error.

[xyph]

You aren't checking the result from mysql_query(). You're checking the result from mysql_num_rows().

 

You should also implement mysql_error when developing to help understand what part of your query is actually failing.

[akrohn]

OK. I have really learned a lot from this back and forth. Seriously, thanks for the people who have made me think about this problem. I added the mysql_error() right after the query, and what to my surprise, but the error was that I had not given that database user permission to SELECT. AAAHHHHH!

 

Problem Solved!

 

Thanks again.

[redsmurph]

Uhuh .

 

[xyph]

A faster way to do this is to implement mysql_errno to perform the check in a single query.

 

Set your `users_email` to unique, and perform the INSERT only then check for errors. If there is an error, check if the errno is 1062, (http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html) which is the error number for duplicate key entry. You then know that the email already exists.