How to check if another account is under the same email address in SQL in a php script?

Question

Do I have to do a query to see how many accounts with that email there are? And do I get the numbeer of rows? How do I tell the user that there is an account with that email already? This is for registering accounts.
Does it go something like this?:
SELECT * FROM Members where email='$email';

then if there is more than one row, warn the user? But how do I send back to javascript to display a message?

Answer 1

For handling the email already in use problem:

$issue = htmlentities(mysql_real_escape_string(stripslashes(GET['issue'])));

if ($issue == "email") {

echo "Email already in use";

}

if ($issue == "invsalid") {

echo "Invalid email";

}



And the code for checking and registering a user:



$email = post['email'];

//Prevent SQL Injection

$email = htmlentities(mysql_real_escape_string(stripslashes($email)));

if ( eregi ( '[a-z||0-9]@[a-z||0-9].[a-z]', $email ) ) { //checks to make sure the email address is in a //valid format

$domain = explode( "@", $email ); //get the domain name



//if ( @fsockopen ($domain[1],80,$errno,$errstr,3)) {

//if the connection can be established, the email address is probably valid

$sql1 = "SELECT * FROM Members where email='$email';";//could use SQL count function instead

$result1 = mysql_query($sql1);

$count1 = mysql_num_rows($result1);

if ($count1 >= 1) {

header("Location: register.php?issue=email");

} else {

//add user to database

}

} else {

header ("Location: register.php?issue=invalid:);

}



You could also use jQuery and AJAX to alert the user if the email is already taken as they type.



Hope this helps!

Answer 2

You should NEVER allow the system to accept more than one entry. So it should be a unique key. On most systems the primary key should be a user id int() with auto-increment. Then the query you use os correct, so you count the number of rows, if it is more than 0 you know there is a conflicting address. So create an error message string, maybe $error_msg .= "This email is already on use". Quite honestly javascript is pretty well useless for database control. Use php. You are netter to use a form with method $_SERVER[PHP_SELF] to call itself on submit, store the entered post details EXCEPT any password and enter them in the form if there is content. Make the form big enough for an additional error text if required. This can be set to display $error_msg every time the form loads, it will show nothing on first viewing the form as no error message is set. It can also be use for "Passwords do not match" for incorrect confirmation password, also if you ask for the email to be entered twice for verification (miss spelling by users signing up can totally fill the system with accounts people do not know the email address for) it an report the error for that. And it is a good place to show "This is not a valid email address" when your verification code finds a false entry, often tried by would be attackers as it stops you contacting them. Also a place they regularly push a hacking script link to gain access to the rest of the site. Make sure they are real email addresses.

Answer 3

I made a script like this. This is what I did:



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

if(mysql_num_rows($result) == 0){

(however you make the account goes here)

}

else

{

echo "There is already an account with this email!";

}

edit: I didn't read that last part! You should be able to just echo the html you use for the javascript...

like echo '';



That should work!