Compare duplicate records in the database with PHP and Mysql

Asked

Viewed 160 times

-1

I am trying to make a condition that prevents you from saving the same login name of another already registered user.

$login_fail=("SELECT DISTINCT $login FROM login");

        if(empty($nome)){
            $errMSG = "Por favor Insira o nome";
        }

        else if($login==$login_fail){
            $errMSG = "Por favor Insira outro login";
        }

I am learning how to use distinct and Gostarian to know how best to compare repeated information that is already in the database

php mysql sql database

  • Ever thought about creating a primary key or declaring the field as unique in your table?

  • I decided to make a query with Where $stmt = $DB_con->prepare("SELECT * FROM usuario WHERE login='$login'");
 $stmt->execute();
 if($stmt->rowCount() > 0) {
 $errMSG = "Por favor Insira um login diferente";
 }

1 answer

0

I am trying to make a condition that prevents you from saving the same login name of another already registered user.

Your question has no relation to the problem itself, because its solution is problematic and does not solve what you want.

Even if your idea worked, it still presents a problem of race-condition.

The solution for your case is to use UNIQUE, add the UNIQUE INDEX in the column where you do not want it to repeat:

ALTER TABLE usuario ADD UNIQUE (login)

This will prevent two data with the same value for the login on the table of usuario. The exception to the rule is null values, the null value can repeat, even with Unique, as long as the column allows null values. Therefore, the login cannot have null enabled.

Once you own UNIQUE, simply do Insert normally:

$stmt = $DB_con->prepare("INSERT INTO usuario VALUES [...] ");

The execution will fail if there is duplicate data, so you could simply use the rows_affected and check if it is greater than zero.

The error code is 1062 by duplicity. So if you want to distinguish one error from another (for example, error because it exceeded the field limit or because it is published) you can use $mysqli->errno (or equivalent) and compare whether the error was because of duplicity or not.

Browser other questions tagged php mysql sql database

You are not signed in. Login or sign up in order to post.