Question 297657 in Mysql Question posted in
The official MySQL documentation can be found here.

how to create user NodeJS + MYSQL

I want to code the registration function with mysql, but when I check the condition, it says Email already exists even though it doesn’t exist in the database.

Why is it that when I register, it always says that the Email or username already exists even though it is not in the database

In this case, I want to check if the email and username already exist in the system. If so, it will say that the email or username already exists. If not, it will allow registration.

code

const bcrypt = require("bcryptjs");
const avatars = require("../databases/avatar.js");
const mysqlConnection = require("../databases/connect-mysql.js");
const jwt = require("jsonwebtoken");
const otpGenerator = require("otp-generator");

function getRandomAvatar() {
  const randomIndex = Math.floor(Math.random() * avatars.length);
  return avatars[randomIndex].avatar;
}

async function register(req, res) {
  try {
    const { USERNAME, EMAIL, PASSWORD, FIRST_NAME, LAST_NAME } = req.body;

    if (!EMAIL) {
      return res.status(400).send("Email cannot be empty");
    }
    // Check if the email already exists in the database
    const emailExistQuery = `SELECT * FROM USERS WHERE EMAIL = ?`;
    const emailAlreadyExists = await mysqlConnection.query(emailExistQuery, [
      EMAIL,
    ]);
    if (emailAlreadyExists.length > 0) {
      return res.status(400).send("Email already exists");
    }

    // Check if the username already exists in the database
    const usernameExistQuery = `SELECT * FROM USERS WHERE USERNAME = ?`;
    const usernameAlreadyExists = await mysqlConnection.query(
      usernameExistQuery,
      [USERNAME]
    );

    if (usernameAlreadyExists.length > 0) {
      return res.status(400).send("Username already exists");
    }

    const salt = bcrypt.genSaltSync(10);
    const hashedPassword = await bcrypt.hash(PASSWORD, salt);

    const insertUserQuery = `
      INSERT INTO users (USERNAME, EMAIL, PASSWORD, AVATAR, FIRST_NAME, LAST_NAME)
      VALUES (?, ?, ?, ?, ?, ?)`;

    const newUserValues = [
      USERNAME,
      EMAIL,
      hashedPassword,
      getRandomAvatar(),
      FIRST_NAME,
      LAST_NAME,
    ];

    await mysqlConnection.query(insertUserQuery, newUserValues);

    return res.status(200).send("User has been created");
  } catch (error) {
    return res.status(500).send({ error: error.message });
  }
}

table mysql

CREATE TABLE USERS (
    ID CHAR(36) DEFAULT (UUID()) PRIMARY KEY,
    USERNAME VARCHAR(255) UNIQUE NOT NULL,
    EMAIL VARCHAR(255) UNIQUE NOT NULL,
    PASSWORD VARCHAR(255),
    EMAIL_VERIFY BOOL DEFAULT FALSE,
    ADMIN BOOL DEFAULT FALSE,
    FIRST_NAME VARCHAR(255),
    LAST_NAME VARCHAR(255),
    DATE_OF_BIRTH DATE,
    MOBILE VARCHAR(255),
    ADDRESS TEXT,
    AVATAR TEXT,
    CREATE_AT DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UPDATE_AT DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

postman

enter image description here

image databse

enter image description here

Tags:

2

Answers


  1. 0

    Are you sure you are connecting to the same DB from your code and through visual DB client? Maybe you connection string is stale or uses wrong DB connection credentials and values.

    Login or Signup to reply.
  2. 0

    You have only selected but not compared, you should edit the code like this

    You must compare the email or username entered and the email or username in the DB

    edit code

    async function register(req, res) {
  try {
    const { USERNAME, EMAIL, PASSWORD, FIRST_NAME, LAST_NAME } = req.body;

    if (!EMAIL || !USERNAME) {
      return res.status(400).send("Email and Username cannot be empty");
    }

    // Check if email or username already exists in the database
    const userExistQuery = `
      SELECT * FROM USERS
      WHERE EMAIL = ? OR USERNAME = ?`;

    const userAlreadyExists = await mysqlConnection.query(userExistQuery, [
      EMAIL,
      USERNAME,
    ]);

    if (userAlreadyExists.length > 0) {
      const existingEmail = userAlreadyExists.find(
        (user) => user.EMAIL === EMAIL
      );
      const existingUsername = userAlreadyExists.find(
        (user) => user.USERNAME === USERNAME
      );

      if (existingEmail) {
        return res.status(400).send("Email already exists");
      }

      if (existingUsername) {
        return res.status(400).send("Username already exists");
      }
    }

    const salt = bcrypt.genSaltSync(10);
    const hashedPassword = await bcrypt.hash(PASSWORD, salt);

    const insertUserQuery = `
      INSERT INTO users (USERNAME, EMAIL, PASSWORD, AVATAR, FIRST_NAME, LAST_NAME)
      VALUES (?, ?, ?, ?, ?, ?)`;

    const newUserValues = [
      USERNAME,
      EMAIL,
      hashedPassword,
      getRandomAvatar(),
      FIRST_NAME,
      LAST_NAME,
    ];

    await mysqlConnection.query(insertUserQuery, newUserValues);

    return res.status(200).send("User has been created");
  } catch (error) {
    return res.status(500).send({ error: error.message });
  }
}

    This will check if the email or username is already in the database before registering

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search