通用错误消息与注册表单一起使用，但需要特定的消息来表示重复的名称

Question

I created a registration form using PHP, html, and Bootstrap. This code works on a basic level, and if a duplicate username is entered it shows a generic error message since 'username' and 'email' are unique in my database. The problem with that is I want the user to understand what went wrong, so they don't repeatedly try to enter the same username or email over and over again. I didn't think about it until I finished the page. Doh!

This is the working php script for the generic message:

//FROM REGISTER.PHP
<?php

    require_once('connect.php'); //<--MY DATABASE FILE
    if(isset($_POST) & !empty($_POST)) {
        $username = mysqli_real_escape_string($connection, $_POST['username']);
        $email = mysqli_real_escape_string($connection, $_POST['email']);
        $password = sha1($_POST['password']);
        $sql = "INSERT INTO `users` (username, email, password) VALUES ('$username', '$email', '$password')";

        $result = mysqli_query($connection, $sql);

    if($result) {
        $successMessage = "User Registration Successful! Please Login.";
    }
    else { //GENERIC MESSAGE FOR ALL ERRORS, INCLUDING DUPLICATES
        $failMessage = "Something went wrong. User Registration failed.";
    }
  }
?>

Here is my snippet of html, php, and Bootstrap:

<div class="container">
    <!-- HERE ARE THE ALERT DIVS THAT POP UP -->
    <?php if(isset($successMessage)){ ?><div class="alert alert-success text-center" role="alert"><?php echo $successMessage; ?> </div><?php } ?>
    <?php if(isset($failMessage)){ ?><div class="alert alert-danger text-center" role="alert"><strong>Error: </strong> <?php echo $failMessage; ?> </div><?php } ?>
    <form class="form-signin" method="POST">

The alerts pop up directly above the form, as seen in these snapshots:

I have tried so many things before coming here to no avail. For example, I tried:

$duplicate = mysqli_query("SELECT username FROM users WHERE username='".$_POST['username']."'");
//ALSO TRIED
//$duplicate = mysqli_query("SELECT username FROM users WHERE username = 'username');

if(mysqli_num_rows($duplicate) > 0) {
    $failMessage = "Username already exists";
}

However, it completely skips over that and gives me the generic Error message. I've seen so many examples/answers on how to prevent duplicates, none of which are helping me figure this out.

I've read about creating separate indexes for username and email, but I would have to do more reading on databases to understand what that entails.

Answer 1

Use the below code:

Require to add $connection->errno == 1062

<?php

        require_once('connect.php'); //<--MY DATABASE FILE
        if(isset($_POST) & !empty($_POST)) {
            $username = mysqli_real_escape_string($connection, $_POST['username']);
            $email = mysqli_real_escape_string($connection, $_POST['email']);
            $password = sha1($_POST['password']);
            $sql = "INSERT INTO `users` (username, email, password) VALUES ('$username', '$email', '$password')";

            $result = mysqli_query($connection, $sql);
    if($result) {
            $successMessage = "User Registration Successful! Please Login.";
    }else{

    if ($connection->errno == 1062) {
          $failMessage = "User already exists";
    }else{
    $failMessage = "Something went wrong. User Registration failed.";
    }

    }
    ?>