Jump to content
PrinceTaz

Check if User and Email already exists

Recommended Posts

Hey, so I'm trying to check the database if the user and email already exists when registering.

<?php
include_once('includes/config.php');

if(isset($_POST['submit'])) {
	$username = $_POST['username'] ? trim($_POST['username']) : null;
	$password = md5($_POST['password']) ? trim($_POST['password']) : null;;
	$email = ($_POST['email']);
	$message = "";

	if(empty($username) || empty($password) || empty($email)) {
		$message = "All fields required";
	} else {
		$sql = "SELECT COUNT(username) AS userNum FROM users WHERE username = :username";
		$sql = "SELECT COUNT(email) AS emailNum FROM users WHERE email = :email";
		$stmt = $db->prepare($sql);

		$stmt->bindValue(':username', $username);
		$stmt->execute();

		$row = $stmt->fetch(PDO::FETCH_ASSOC);

		if($row['userNum'] >0) {
			die("That username already exists!");
		} elseif($row['emailNum'] > 0) {
			die("That email already exists!");
		}

		$sql = "INSERT INTO users (username, password, email) VALUES (:username, :password, :email)";
		$stmt = $db->prepare($sql);

		$stmt->bindValue(':username', $username);
		$stmt->bindValue(':password', $password);
		$stmt->bindValue(':email', $email);

		$result = $stmt->execute();

		if($result) {
			$message = "Registration was successful";
		}

	}
}
?>

If I remove this line "

$sql = "SELECT COUNT(email) AS emailNum FROM users WHERE email = :email";

the code works but only checks the username. How can I check both?

Share this post


Link to post
Share on other sites

So you want to check if the username is taken or the email is taken?

Share this post


Link to post
Share on other sites

define the column(s) in your table as unique indexes. insert the data and detect if a duplicate key error occurred. in the error handling logic, execute one select query to find which column(s) contain the same values you just tried to insert.

  • Like 1

Share this post


Link to post
Share on other sites
23 hours ago, requinix said:

So you want to check if the username is taken or the email is taken?

Yes. Check if either the username or email has already been taken.

23 hours ago, mac_gyver said:

define the column(s) in your table as unique indexes. insert the data and detect if a duplicate key error occurred. in the error handling logic, execute one select query to find which column(s) contain the same values you just tried to insert.

So they are both already unique.  Is the problem with my query? I'm still pretty new at this so I'm having a hard time understanding what you're saying. Where exactly in my code should I start from in terms of rewriting?

$sql = "SELECT COUNT(username) AS userNum FROM users WHERE username = :username";
$sql = "SELECT COUNT(email) AS emailNum FROM users WHERE email = :email";

Is this the issue here? Does $sql get rewritten as a email query and not as a username?

Share this post


Link to post
Share on other sites

Answer us this:

What line are you trying to execute:

		$sql = "SELECT COUNT(username) AS userNum FROM users WHERE username = :username";
		$sql = "SELECT COUNT(email) AS emailNum FROM users WHERE email = :email";
		$stmt = $db->prepare($sql);

You have tried to define two queries but only the second one is going to be run.  You could change this query to simply do a where clause looking for a match on the user or the email.

Share this post


Link to post
Share on other sites
SELECT COUNT(*) AS recs
FROM users
WHERE username = :username
	OR email = :email

You're overwriting your query and only checking for the email match - try the above as the only value of $sql. Also (and I could be wrong so hopefully someone will correct me if I am) I seem to remember having run into to troubles using 'email' as a column name in MySql - I tend to use something like "email_address" (or "eAddy" if I'm tired of typing).

As to the unique index point that mac_gyver raised, if those columns are already set to 'UNIQUE', just insert the data. If there's a duplicate in either column, the insert will throw an error - check that and let the user know what's up. No data will actually be inserted because the attempt violates the unique constraints so no harm done.

Share this post


Link to post
Share on other sites
3 hours ago, maxxd said:

SELECT COUNT(*) AS recs
FROM users
WHERE username = :username
	OR email = :email

You're overwriting your query and only checking for the email match - try the above as the only value of $sql. Also (and I could be wrong so hopefully someone will correct me if I am) I seem to remember having run into to troubles using 'email' as a column name in MySql - I tend to use something like "email_address" (or "eAddy" if I'm tired of typing).

As to the unique index point that mac_gyver raised, if those columns are already set to 'UNIQUE', just insert the data. If there's a duplicate in either column, the insert will throw an error - check that and let the user know what's up. No data will actually be inserted because the attempt violates the unique constraints so no harm done.

Okay so now with that, how would I check if the username AND email already exists?

I have this for the username:

if($row['userNum'] >0) {
			die("That username already exists!");
}

How do I update it to check for email as well?

Share this post


Link to post
Share on other sites
28 minutes ago, PrinceTaz said:

How do I update it to check for email as well?

Do you know what maxxd's code is? Can you describe what it does?

Share this post


Link to post
Share on other sites
14 minutes ago, requinix said:

Do you know what maxxd's code is? Can you describe what it does?

It's a SQL query which selects an argument to check against the same argument value in the database to check if they are the same?

Share this post


Link to post
Share on other sites
5 hours ago, PrinceTaz said:

It's a SQL query which selects an argument to check against the same argument value in the database to check if they are the same?

The point is, that Maxxd's query does exactly what you are asking for.  It checks if a particular row has a matching username OR a matching email.  

 

Share this post


Link to post
Share on other sites
On 6/1/2019 at 7:00 PM, requinix said:

Do you know what maxxd's code is? Can you describe what it does?

 

18 hours ago, gizmola said:

The point is, that Maxxd's query does exactly what you are asking for.  It checks if a particular row has a matching username OR a matching email.  

 

A better question would have been, do I create an elseif but with "$row[''email"] > 0"?

Share this post


Link to post
Share on other sites
13 hours ago, PrinceTaz said:

A better question would have been, do I create an elseif but with "$row[''email"] > 0"?

Not as it is now - if you want to tell the user which is taken you'll have to update the query. Right now it just returns a count of records that match either the username or the email. You'll have to actually select both and then check in PHP which one matches, or rewrite the query to return the offending column.

However, I'd recommend just letting people know that one of the two has been taken. That way you're not confirming to an outside party which of the two actually exists in the database - a hacker that knows for a fact a username exists has less work to do and can focus only on figuring out a correct password.

  • Like 1
  • Great Answer 1

Share this post


Link to post
Share on other sites
1 hour ago, maxxd said:

Not as it is now - if you want to tell the user which is taken you'll have to update the query. Right now it just returns a count of records that match either the username or the email. You'll have to actually select both and then check in PHP which one matches, or rewrite the query to return the offending column.

However, I'd recommend just letting people know that one of the two has been taken. That way you're not confirming to an outside party which of the two actually exists in the database - a hacker that knows for a fact a username exists has less work to do and can focus only on figuring out a correct password.

Wow, I never thought of it like that. I've always wondered why big websites don't have that feature, I always thought it was because it would be too complicated to attempt.

Share this post


Link to post
Share on other sites

Same goes for user login. Don't tell them if they got the username wrong or the password wrong, just tell them it's an invalid login.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.