Login with username or email - MySQL [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the 开发者_如何学JAVAquestion so it can be answered with facts and citations by editing this post.

Closed 1 year ago.

Improve this question

I'm planning on letting users login with their email or username but I'm wondering on how to do this correctly in MySQL.

I'm currently validating the username and email fields (PHP) to ensure that they aren't already in the DB. These queries occur before the actual insert query that will be adding the user. I imagine adding an unique index is enough for a system which logs in with a single identity - but what about in this case where uniqueness is required for two fields? Is adding an unique index for each column enough?

Also, are there any problems I might encounter with this method?

Thanks in advance.

---

I wouldn't rely on the index - better to do a search in the database for each of the two values first. A unique index is worth adding as well, but never great to rely on for catching these events.

On a site where I do the same with logins, if the username has an @ symbol I assume it is an email and look up the corresponding username (which I need, because passwords are hashed with usernames as salt in that system).

---

I do this on my site, but I've kept it relatively simple. I don't allow @ symbols in usernames. Before firing my login query on the users table in my function, I use php to determine if there's a @ in the username field. If so, I condition the query (with a variable for the fieldname) to check username against the email field in the user table.

There is likely a way to make it happen directly in MySql, but for me this solution is fast, easy to program, and works 100% of the time.

---

I would strongly advocate being able to just login with the email address - they don't want to remember yet another username and you don't want to have to deal with checking if you're looking at a username or an email address each time you want to validate it. But that said, it's not a big deal, just something you can do without. If you feel that this would be a good feature for your users, by all means, go ahead.

Do NOT rely on a unique index to catch your duplicates for you, that's not a clean way of doing it. ALWAYS check before inserting if the values already exist.

Another tip that comes to mind is - if for any error, you have to refresh the form, please ensure all the user filled fields are populated back.