尝试在用户名存在时弹出错误

Question

I have seen many articles on this same question and have been unable to translate their answer to my problem. Basically i made my field 'username' unique in my database and would like to catch an the error of existing username in databse and echo something else out more user friendly. As of now i am able to get the error message but unable to control it. I simply want to let the user know when the username already exist in the database. Any help would be greatly appreciated.

This is the error i get when adding a member with a already used 'username'.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'theller' for key 'username'' in /Applications/MAMP/htdocs/TESTING/PHP/PHP-TEST/addMember.php:42 Stack trace: #0 /Applications/MAMP/htdocs/TESTING/PHP/PHP-TEST/addMember.php(42): PDOStatement->execute(Array) #1 {main} thrown in /Applications/MAMP/htdocs/TESTING/PHP/PHP-TEST/addMember.php on line 42

here is my script that is called when my ajax script executes and send over its data.

<?php ob_start();
          require_once 'classes/Config.php';
          try {
              $conn = new PDO('mysql:host=' . Config::get('mysql/host') . ';dbname=' . Config::get('mysql/db'), Config::get('mysql/username'), Config::get('mysql/password'));
              $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              $data = $conn->query('SELECT * FROM Testing');
          } catch(PDOException $e) {
                if(($PDO->errorCode() == 23000) || ($PDOStatement->errorCode() == 23000)) {
                  echo 'USER ALREADY EXIST';
                } 
                else {
                 // not a dupe key rethrow error
                 echo 'error';
                }
            }


        $form = $_POST;
        $firstname = htmlspecialchars($form[ 'firstname' ]);
        $lastname = htmlspecialchars($form[ 'lastname' ]);
        $username = htmlspecialchars($form[ 'username' ]);
        $password = htmlspecialchars($form[ 'password' ]);
        $passMatch = htmlspecialchars($form[ 'pass_match' ]);
        $message = htmlspecialchars($form[ 'message' ]);
        $email = htmlspecialchars($form[ 'email' ]);

        if (empty($firstname) || empty($lastname) || empty($username) || empty($password)|| empty($passMatch)|| empty($message)|| empty($email)){
            $error = "Complete all fields";
        }
        // Password match
        if ($password != $passMatch){
            $error = "Passwords don't match";
        }
        // Email validation
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)){
            $error = "Enter a  valid email";
        }
        if(IsInjected($email)) {
            $error = "Bad email value!";
        }
        if(!isset($error)){
            $sql = "INSERT INTO Testing ( firstname, lastname, message, email, username, password ) VALUES( :firstname, :lastname, :message, :email, :username, :password )";
            $query = $conn->prepare( $sql );
            $query->execute( array( ':firstname'=>$firstname, ':lastname'=>$lastname, ':message'=>$message, ':email'=>$email, ':username'=>$username, ':password'=>$password ));
            if(!$query->rowCount() > 0){
                echo "exists! cannot insert";
            }else{
            header('Location: thankyou.php'); 
            }

        }
        else{
            echo "error occured: ".$error;
            exit();
        }
?>

Answer 1

I had similar problem in my recent project. Please try to replace your catch statement with the following:

catch(PDOException $e) {
    if(isset($conn))
    {
        if($conn->errorInfo()[0] == 23000) {
            echo 'USER ALREADY EXIST';
        } 
        else {
        // not a dupe key rethrow error
            echo 'error';
        }
    }
}

Information about SQLSTATE error resides in an errorInfo array of the PDO object used for the query