How to do to see if there is already a record in the database mysql and php [duplicate]

0

Good afternoon community,

I am currently reviewing some concepts of html, php, css, mysql, and I have been building a simple login system with a registration form, through some videos. So far so good, taking the time when I managed to register two customers with the same username. I spent a few hours in searches and tried to change my code, but I was never able to build a function to check if a client with the same name already exists in the database.

If someone can point me in the right direction for me to verify, I am grateful.

This is the code that treats everything about 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:)

    
asked by anonymous 11.06.2017 / 16:42

1 answer

5

Give% column%, so it will prevent MySQL from having two equal information in that column, for example:

ALTER TABLE utilizadores ADD UNIQUE (email)

This will make UNIQUE unique, that is, you can not have two identical emails.

Then do:

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

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

All responsibility for preventing duplication will be from MySQL. If you attempt to insert an existing data it will not be inserted, so email will be mysqli_affected_rows .

Another way, without using 0 would be to SELECT an INSERT after:

$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, usually 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, using UNIQUE on large tables can make the insertion process slower for obvious reasons. Anyway, choose one.

In addition, PHP has support for UNIQUE (BCrypt and in future Argon2i) as well as password_hash both are extremely easy to implement, as much as hash_pbkdf2 . In addition, you already have Libsodium , which supports Argon2i and SCrypt. There is no reason to use something considered broken from 1994 as MD5, get this , this and maybe this .

    
11.06.2017 / 17:25