Jump to content

Check if User and Email already exists


PrinceTaz

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?

Link to comment
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?

Link to comment
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.

Link to comment
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.

Link to comment
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?

Link to comment
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.  

 

Link to comment
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"?

Link to comment
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
Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.