如何在单个查询中获得两个单独的错误结果

Question

trying to make basic user registration script with PDO, when user and email exists i get single error message (user or email already exists), what im trying to get separate output for user and email errors. Also any other suggestions about this script much appreciated

if(isset($_POST['Register'])){
$username = $_POST['username'];
$email = $_POST['email'];
$password = $_POST['password'];
//Validating ???


$statement  = $conn->prepare("SELECT COUNT(*) AS count FROM users 
    WHERE username = :username AND email = :email");
$statement->bindParam(':username', $username);
$statement->bindParam(':email', $email);
$statement->execute(array(':username' => $username, ':email' => $email));

    while ($row = $statement ->fetch(PDO::FETCH_ASSOC)) {
      $result = $row["count"];
        }
        if ($result > 0) {
            echo "That usernam or email is already taken";

            }
        else {
            $sql = ("INSERT INTO users(username, email, password) VALUES(?, ?, ?)");
            $statement = $conn->prepare($sql);
            $statement->bindValue(":username", $username, PDO::PARAM_STR);
            $statement->bindValue(":password", $password, PDO::PARAM_STR);
            $statement->bindValue(":email", $email, PDO::PARAM_STR);
            $statement->execute(array("$username", "$email", "$password"));
            echo "New record created successfully";
            }
}

Answer 1

I would probably do this:

SELECT
   COUNT(IF (username IS NOT NULL AND username != '', 1, NULL)) AS username_taken
 , COUNT(IF (email IS NOT NULL AND email != '', 1, NULL)) AS email_taken
FROM users 
WHERE LOWER(username) = LOWER(TRIM(:username))
OR LOWER(email) = LOWER(TRIM(:email))

Note, the reason I'm using COUNT() here is to aggregate if there are two result rows. COUNT() ignores NULL, so this will compress two rows to one and return 1 or 0 (from COUNT()), or simply provide 1 for both columns if it's the same row.

Also, as @Fred -ii- points out, you've got the wrong PDO method call for the type of binding you're doing later on. So:

$sql = "
INSERT INTO users (
   username, email, password
) VALUES (
   TRIM(:username), TRIM(:email), :password
)
";

$statement = $conn->prepare($sql);

$statement->bindParam(":username", $username, PDO::PARAM_STR);
$statement->bindParam(":password", $password, PDO::PARAM_STR);
$statement->bindParam(":email", $email, PDO::PARAM_STR);