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

Question

尝试使用 PDO 制作基本的用户注册脚本，当用户和电子邮件存在时，我收到一条错误消息(用户或电子邮件已存在)，我试图为用户和电子邮件错误获取单独的输出。另外，非常感谢有关此脚本的任何其他建议

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

我可能会这样做:

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))

请注意，我在这里使用 COUNT() 的原因是为了在有两个结果行时进行聚合。 COUNT() 忽略 NULL，因此这会将两行压缩为一行并返回 1 或 0(来自 COUNT())，或者如果两列是同一行，则只需为两列提供 1。

此外，正如 @Fred -ii- 指出的那样，对于稍后要执行的绑定(bind)类型，您的 PDO 方法调用是错误的。所以:

$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);