How I optimised a registration form

“A pocket watch in a person's open hand” by Veri Ivanova on Unsplash

I always want to learn something new and if an opportunity comes I try to make use of it to achieve the goal of learning.

So, recently got to revamp login and signup pages, I decided to use React as a front end because I wanted to learn React and use the existing backend written in PHP, as it would be quick.

I created the login page first with little improvements like adding Facebook and Google login. I used the react-facebook-login and react-google-login modules of React for this functionalities.

After completing the login page, I moved to the signup page and was checking the backend code for signup I came across a block of code like this —

Problem

“Why we need to run two different queries for just inserting a data ?”

If you are querying over a network, then there is overhead in using number of queries instead of a single query.

The query :

returns the number of rows where email = email if email exists then user already registered else insert a new user.

For me, the problem is there must be a better way to do this check without running the first query that returns all the emails.

What does a sign-up page require?

So, the features that signup/registration pages required are —

1. Do not allow duplicate entry.
2. Give the user a feedback about what’s going on.

Solve for duplicate Entry

For this, I moved my attention to Table schema,

Every RDMS provide this very handy key constraint for a column called Unique Key.

A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table.

So, I changed the schema of the registration table and made the email column unique.

By only doing this we can prevent duplicate entry in the column and the database will throw an error if we try to insert a duplicate entry in the email column.

Give some feedback to the user

Now, the second part of the problem is to give the user a feedback about what’s going on i.e. what happened, why not proceeding to the next step of registration.

As I mentioned above, that database will throw an error when you try to insert a duplicate value in a column with unique key constraint.

We can just send this error to the user as feedback.

But the problem is we can not just show the error shown by MySQL to our user because that error contains information related to table schema. Hence for security reason, we don’t want to show anything from backend of our system to a user.

So, all we want the error code that MySQL and if it’s a duplicate entry error, we will show our user custom error message telling ‘email already registered ‘.

So, I googled the list of errors that MySQL throws when an insertion fails due to duplicate value. In that list, I found the error code for duplicate entry — MySQL Error Codes

Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)

Message: Duplicate entry ‘%s’ for key %d

Now we can just compare error code with this and if True, show the error message.

Lets breakdown the above code block —

mysql_errno() = Returns the error number from the last MySQL function, or 0 (zero) if no error occurred.
In the IF block, we are checking if $errorCode is True i.e. non-zero, which means insertion failed for some reason. 
Then, we apply a Ternary Condition, $errorCode equals to 1062 (the duplicate value insertion error code) show `Email already registered` else `Something went wrong`.
If $errorCode is 0(zero) then no error occurred. Therefore show a success.

By this user will get a proper feedback.

Highlights —

1. Use Unique Key constraint to not allowing duplicate entry in any column.
2. Utilise Error codes given by MySQL to debug and analysis of your SQL query.