SQL for users table

The following is the SQL for users table. Note, we are using MySQL as RDBMS in our example. There can be a slight change in the queries for other databases:

CREATE TABLE `blog`.`users` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `name` VARCHAR(100) NOT NULL ,
 `email` VARCHAR(50) NOT NULL ,
 `password` VARCHAR(50) NOT NULL ,
 PRIMARY KEY (`id`), 
 UNIQUE `email_unique` (`email`))
ENGINE = InnoDB;

This query will create a posts table as described above. The only thing that we haven't discussed is the database engine. The last line of this query ENGINE = InnoDB sets the database engine for this table as InnoDB. Also, on line 1, `blog` represents the name of the database. If you have named the database anything else other than blog, replace it with your database name.

We are only going to write the API's endpoint for posts and comments and are not going to write the endpoints for users, so we will add data to the users table manually using SQL Insert Queries.

Here are the SQL Insert Queries to populate the users table:

INSERT INTO `users` (`id`, `name`, `email`, `password`)
 VALUES 
(NULL, 'Haafiz', 'kaasib@gmail.com', '$2y$10$ZGZkZmVyZXJlM2ZkZjM0Z.rUgJrCXgyCgUfAG1ds6ziWC8pgLiZ0m'), 
(NULL, 'Ali', 'abc@email.com', '$2y$10$ZGZkZmVyZXJlM2ZkZjM0Z.rUgJrCXgyCgUfAG1ds6ziWC8pgLiZ0m');

As we are inserting two records having the name, email, and password, we set id to null. Since it is auto-incremented, it will be set automatically. Also, you can see a long random string in both records. This random string is the password. We set the same password for both users. However, the user will not be entering this random string as the password. This random string is encrypted an version of the user's actual password. The user's password is qwerty. This password was encrypted using the following PHP code:

password_hash("qwerty", PASSWORD_DEFAULT, ['salt'=>'dfdferere3fdf34dfdfdsfdnuJ$er']);
/* returns $2y$10$ZGZkZmVyZXJlM2ZkZjM0Z.rUgJrCXgyCgUfAG1ds6ziWC8pgLiZ0m
*/

The password_hash() function is the PHP recommended function for encrypting passwords. The first parameter is a password string. The second parameter is an encryption algorithm. While, the third parameter is an options array where we set a random string as salt. You can add a different salt as well.

However, this salt needs to be fixed for encrypting passwords every time because this encryption is a one-way encryption. That means the passwords can't be decrypted. So every time you need to match passwords, you will always have to encrypt the user provided password and match it with the one in the database. In order to match the user provided password and the one in the database, we need to use the same password function with the same arguments.

We are not making the user login functionality now, however, we will do that later.