Checking the uniqueness of a record, how does it work?

Question

PHP

Hello, please explain how the uniqueness of a database record works, or rather its verification?

I want it to throw an error when a certain record in the database matches. To do this, I set the Index for the column. But so far, unfortunately, one option works, it's too crooked. The second one doesn't work at all.

1st option:

$stmt = $dbh->prepare("INSERT INTO users (name, lastname, email, password) VALUES (:name, :lastname, :email, :pass)");
        $stmt->bindParam(':name', $name_t);
        $stmt->bindParam(':lastname', $lastname_t);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':pass', $hashed_password);
        $falg = $stmt->execute();

        if ($falg) {
            header ("Location: index.html");
            die;
        } else {
            echo "Данная электронная почта уже используется. ";?><a href="enter.php" style="color: red">Войдите</a> <?php echo "по этой почте или используйте другую";
            die;
        }

It works crookedly here, gives an error, if I'm not mistaken "00000", and I immediately consider it to be a match of the record, and indicate that there is such a record. But this happens after the page is reloaded, with plain text at the top of the page and that's it. What is crooked.

2nd option:

$stmt = $dbh->prepare("INSERT INTO users (name, lastname, email, password) VALUES (:name, :lastname, :email, :pass)");
        $stmt->bindParam(':name', $name_t);
        $stmt->bindParam(':lastname', $lastname_t);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':pass', $hashed_password);

        $stmt->execute();

        if ($stmt->fetch(PDO::FETCH_NUM)) {
            echo "Данная электронная почта уже используется. ";?><a href="enter.php" style="color: red">Войдите</a> <?php echo "по этой почте или используйте другую";
        }else{
            echo "Успешно";
        }

Which unfortunately doesn't work. Here I thought, to catch such a record in the database, and indicate that it exists. Or output successfully.

This option works for me on another page, and it displays a record in the same form that everything is good or bad. Something like this:

if ( !empty($pwd) && !empty($email) ) {
                        $stmt = $dbh->prepare('SELECT email, password FROM users WHERE email = :email');
                        $stmt->bindParam(':email', $email);
                        $stmt->execute();
                
                        $user_p = $stmt->fetch(PDO::FETCH_OBJ);
                
                        if ($user_p){
                            if(password_verify($pwd, $user_p->password)){
                                echo "Успешно";
                            }else{
                                echo '<p class="wrong">Неверный Email или пароль</p>';
                            }
                        }else{
                            echo '<p class="wrong">Неверный Email или пароль</p>';
                        }
                    }else{
                        echo '<p class="noValues">Пожалуйста, заполните все поля</p>';
                    }

But unfortunately it doesn't work here. Please tell me in which direction to move?

Answer 1

I want it to throw an error when a certain record in the database matches. To do this, I set the Index for the column.

The email field in the database must be UNIQUE INDEX, then when inserting a duplicate, MySQL will throw an error that can be processed.
Or you can use the construction INSERT IGNORE ... ON DUPLICATE KEY, for example, to return the ID of the record with this email.