Check if User and Email already exists

[PrinceTaz]

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?

[requinix]

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

[mac_gyver]

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.

[PrinceTaz]

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?

[ginerjm]

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.

[maxxd]

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.

[PrinceTaz]

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?

[requinix]

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?

[PrinceTaz]

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?

[gizmola]

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.  

 

[PrinceTaz]

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"?

[maxxd]

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.

[PrinceTaz]

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.

[Barand]

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.