Check If Username and Email Already Exists in Database

check if username and email already exists in database

First of all, session_start() is called 2 times.
Remove the repeated call inside if ($password == $password2) {

You code is also missing a ; and some } (for properly closing your if conditions)

Now the solution:

Before you can process Database query's result, you need to connect to a DB and execute appropriate SQL command, only then you will get the result you want. Your code is missing this process.

Check my comments in your code below↓ and then check again in corrected code

if ($password == $password2) {
        session_start(); // remove this repeated call
        $password = ($password);
        $sql="select * from account_info where (username='$username' or email='$email')";
        if (mysqli_num_rows($res) > 0) { // $res isn't defined
          $row = mysqli_fetch_assoc($res);
          if ($username==$row['username'])
          {
              $_SESSION['message'] = "Username je vec registrovan";
          }
          else($email==$row['email']){  // `else` doesn't work this way, use `elseif`
              $_SESSION['message'] = "Email je vec registrovan"  // ; missing
          }

Corrected Code:

if (isset($_POST['register_btn'])) {
    $name      = mysqli_real_escape_string($db, $_POST['name']);
    $lastname  = mysqli_real_escape_string($db, $_POST['lastname']);
    $username  = mysqli_real_escape_string($db, $_POST['username']);
    $email     = mysqli_real_escape_string($db, $_POST['email']);
    $password  = mysqli_real_escape_string($db, $_POST['password']);
    $password2 = mysqli_real_escape_string($db, $_POST['password2']);

    if ($password == $password2) {
        $password = ($password);
        $sql      = "SELECT * FROM users WHERE (username='$username' OR email='$email')";
        $res      = mysqli_query($db, $sql); // you were calling $res but it wasn't defined; this connects to the DB and executes SQL and then assigns the result
        if (mysqli_num_rows($res) > 0) {
            $row = mysqli_fetch_assoc($res);
            if ($username == $row['username']) {
                $_SESSION['message'] = "Username je vec registrovan";
            } elseif ($email == $row['email']) {  // changed `else` to `elseif` to include the condition, `else` doesn't accept conditional checks
                $_SESSION['message'] = "Email je vec registrovan";  // added ;
            }
        } else {
            $sql = "INSERT INTO users (name, lastname, username, email, password) VALUES ('$name', '$lastname', '$username', '$email', '$password')";
            if (mysqli_query($db, $sql)) {
                // New record inserted
                $_SESSION['message'] = "Sada si ulogovan";
                $_SESSION['message'] = $username;
                header("location: login.php");
            } else {
                echo("Error: " . mysqli_error($db));
            }
        }
    } // required to close the password checking condition
    else {
        $_SESSION['message'] = "Ne podudaraju se lozinke!";
    }
}

Suggestions:

1. Use prepared statement instead of directly passing user provided input into SQL
   (critical, your current code is vulnerable to SQL injection)
2. Use an IDE that supports PHP and offers syntax highlighting (Atom, Visual Studio Code, PhpStorm etc.)

How to check if the email already exists in database

You're only inserting when there is an error and your database query won't throw any error for both the cases, if it will not find any results or if it will find any results.

What will be in the results of those database queries -

• When database has entry for that email will return array of rows containing that email.
• when database doesn't have entry with that email it will return an empty array.

So, What you can do is, check for the query result length, if result length is 0 then you can insert and if greater than 0. You should send response of "Already Registered" or something.

MySQL - Check if username already exists before submitting the form

If you want to check before submitting the form you can go for ajax only.check after click the submit button this will work you can try this out.

<?php
    $servername = "xxxxxxxxx";
    $username = "xxxxxxxxxx";
    $password = "";
    $dbname = "test";
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    if(isset($_POST['submit'])) {
        $username=$_POST['username'];
        $email = $_POST['email'];
        $password = $_POST['password'];

        //Query statement with placeholder
        $query = "SELECT fname
                  FROM person
                  WHERE fname = '$username'";

        // Execute it
        try {
            $stmt = $conn->prepare($query);
            //for you no need to pass parameter inside execute statement
            $result = $stmt->execute();
            //After executing the query store the result like below
            $stmt->store_result();
        } catch(PDOException $ex) {
            echo $ex->getMessage();
        }

        //Now Check for the row count
       //you have to check numrows >0 like this
       if($stmt->num_rows>0) {
           echo "Account Exists";
           die;
       } else {
           $sql = "INSERT INTO person(username,email,password) VALUES ('$username','$email','$password')";

          if ($conn->query($sql) === TRUE) {
             echo "New record created successfully";
          } else {
             echo "Error: " . $sql . "<br>" . $conn->error;
          }
      }
   }
   $conn->close();
?>

check if username and email already exists with expressjs validator and mysql

This code works for me:

const express = require('express');
const router = express.Router();
const { check,validationResult } = require('express-validator');
const bcrypt = require('bcrypt');
const bcryptRounds = 10;

    router.post('/register', [
        check('username')
            .exists()
            .trim()
            .matches(/^[a-zA-Z\ö\ç\ş\ı\ğ\ü\Ö\Ç\Ş\İ\Ğ\Ü ]{3,16}$/)
            .withMessage('Invalid username!'),
        check('mentionName')
            .exists()
            .trim()
            .matches(/^(?=.*[a-z])[a-z0-9_]{3,15}$/)
            .custom(async mentionName => {
                const value = await isMentionNameInUse(mentionName);
                if (value) {
                    throw new Error('Mention name is already exists!!!');
                }
            })
            .withMessage('Invalid mention name!!!'),
        check('email')
            .exists()
            .isLength({ min: 6, max: 100 })
            .isEmail()
            .normalizeEmail()
            .trim()
            .custom(async email => {
                const value = await isEmailInUse(email);
                if (value) {
                    throw new Error('Email is already exists!!!');
                }
            })
            .withMessage('Invalid email address!!!'),
        check('password')
            .exists()
            .isLength({ min: 6, max: 16 })
            .escape()
            .trim()
            .withMessage('Invalid password!!!'),
        check('rePassword').exists().custom((value, { req }) => {
            if (value !== req.body.password) {
              throw new Error('The passwords is not same!!!');
            }    
            return true;
          })
      ],
      function (req, res) {
        const errors = validationResult(req);
        if (!errors.isEmpty()) {
            return res.status(422).json({ errors: errors.array() });
        } else {
            console.log("----->START USER REGISTRATION");
            const username = req.body.username;
            const mentionName = '@'+req.body.mentionName;
            const email = req.body.email;
            const pass = req.body.password;
            bcrypt.hash(pass, bcryptRounds, function(err, hash) {
                console.log("HASH PASS : "+hash);
                //INSERT USER
            });
        }
    });

    function isMentionNameInUse(mentionName){
        var conn = require('../../modules/mysql_db');
        return new Promise((resolve, reject) => {
            conn.query('SELECT COUNT(*) AS total FROM users_table WHERE m_name = ?', [mentionName], function (error, results, fields) {
                if(!error){
                    console.log("MENTION COUNT : "+results[0].total);
                    return resolve(results[0].total > 0);
                } else {
                    return reject(new Error('Database error!!'));
                }
              }
            );
        });
    }

    function isEmailInUse(email){
        var conn = require('../../modules/mysql_db');
        return new Promise((resolve, reject) => {
            conn.query('SELECT COUNT(*) AS total FROM users_table WHERE email = ?', [email], function (error, results, fields) {
                if(!error){
                    console.log("EMAIL COUNT : "+results[0].total);
                    return resolve(results[0].total > 0);
                } else {
                    return reject(new Error('Database error!!'));
                }
              }
            );
        });
    }

Checking if username already exists in db prepared statement

selec count(*) ... always returns 1 record, with the count. So, when you check else if($stmt->num_rows > 0), then it always returns true. You should check the value returned by count, not the number of records.

---

Related Topics