How to see if there is already a record in the mysql and php database

Question

Good afternoon community,

I am currently revising some concepts of html, php, css, mysql, and was building a simple login system with a registration form, through some videos. So far so good, except for the moment I was able to register two clients with the same username. I spent a few hours researching and tried to change my code, but I was never able to build a function to check if there is already a client with the same name in the database.

If anyone could point me in the right direction to get the check, I’d be grateful.

This is the code that handles everything concerning the server:

<?php
session_start();
$username = "";
$email = "";
$errors = array();
    //liga à base de dados
    $db = mysqli_connect("localhost", "root", "root", "mobies");
        //se o botão de registar for clicado
        if (isset($_POST['register'])){
            $username = mysql_real_escape_string($_POST['username']);
            $email = mysql_real_escape_string($_POST['email']);
            $password_1 = mysql_real_escape_string($_POST['password_1']);
            $password_2 = mysql_real_escape_string($_POST['password_2']);
            //faz com que os espaços estejam bem preenchidos
            if (empty($username)){
                array_push($errors, "Username is required");
            }
            if (empty($email)){
                array_push($errors, "Email is required");
            }
            if (empty($password_1)){
                array_push($errors, "Password is required");
            }
            if ($password_1 != $password_2){
                array_push($errors, "The two passwords do not match");
            }
            //se não houver erros, salva o novo utilizador na base de dados
            if (count($errors) == 0) {
                $password = md5($password_1);
                $sql = "INSERT INTO utilizadores (username, email, password) VALUES ('$username', '$email', '$password')";
                mysqli_query($db, $sql);
                $_SESSION['username'] = $username;
                $_SESSION['success'] = "You are now logged in";
                header('location: index.php');
            }
        }
//login através do formulário de login
if (isset($_POST['login'])){
    $username = mysql_real_escape_string($_POST['username']);
    $password = mysql_real_escape_string($_POST['password']);
    //faz com que os espaços estejam bem preenchidos
    if (empty($username)){
        array_push($errors, "Username is required");
    }
    if (empty($password)){
        array_push($errors, "Password is required");
    }
    if (count($errors) == 0) {
        $password = md5($password);
        $query = "SELECT * FROM utilizadores WHERE username='$username' AND password='$password'";
        $result = mysqli_query($db, $query);
        if (mysqli_num_rows($result) == 1){
            $_SESSION['username'] = $username;
            $_SESSION['success'] = "You are now logged in";
            header('location: index.php');
        } else {
            array_push($errors, "Wrong username/password combination");
        }
    }
}
//logout
if (isset($_GET['logout'])){
    session_destroy();
    unset($_SESSION['username']);
    header('location: login.php');
}
?>

Thank you :)

Answer 1

Give a UNIQUE in the column, this will prevent Mysql from having two equal information in that column, example:

ALTER TABLE utilizadores ADD UNIQUE (email)

That will make the email be unique, ie can not have two equal emails.

---

Then do:

mysqli_query($db, "INSERT INTO ...");

if(mysqli_affected_rows($db) === 1){
    // Deu certo
}

All responsibility to prevent duplication will be Mysql. If you try to insert an existing data it will not be inserted, so mysqli_affected_rows will be of 0.

---

Another way, without using the UNIQUE would make a SELECT of then an INSERT:

$evitaDuplicacao = mysqli_query($db, "SELECT `` FROM ... WHERE email = '$email'") 

if(mysqli_num_rows($evitaDuplicacao) === 0){
   // Não existe um dado repetido, logo:
   mysqli_query($db, "INSERT INTO ...");
}

Each has an advantage, in general I prefer the first, using UNIQUE. The second method is exposed to a race condition and this solution exists the execution of two different queries. However, the use of UNIQUE in large tables can make the insertion process slower, for obvious reasons. Finally, choose a.

---

On the other hand, PHP has support for password_hash (Bcrypt and in the future Argon2i), as well as the hash_pbkdf2 both are extremely easy to implement, as far as the md5(). Besides already having the Libsodium, that supports Argon2i and Scrypt. There is no reason to use something considered broken since 1994 like the MD5, see this, this and maybe this.