mysql - PDO UNION 函数仅处理第一个 SELECT 语句

Question

好吧，我有三种不同类型的用户(开发人员、设计师和雇主)，他们都有不同的数据库表。我已经成功开发了一个脚本，可以使用 UNION 从三个用户表中的任何一个登录用户。不过，我现在在注册时引入了电子邮件验证，因此我需要测试“已确认”字段(在每个表中)是否具有值“1”来处理成功登录(如果该值为 0，则用户将不会可以登录)。

    public function email_confirmed($email) {

        $query = $this->db->prepare("SELECT 
            COUNT(developers.id) FROM " . DB_NAME . ".developers WHERE developers.email=  ? AND developers.confirmed = ? 
            UNION SELECT COUNT(designers.id) FROM " . DB_NAME . ".designers WHERE designers.email = ? AND designers.confirmed = ? 
            UNION SELECT COUNT(employers.id) FROM " . DB_NAME . ".employers WHERE employers.email = ? AND employers.confirmed = ?
            ");
        $query->bindValue(1, $email);
        $query->bindValue(2, 1);
        $query->bindValue(3, $email);
        $query->bindValue(4, 1);
        $query->bindValue(5, $email);
        $query->bindValue(6, 1);

        try{

            $query->execute();
            $rows = $query->fetchColumn();

            if($rows == 1){
                return true;
            }else{
                return false;
            }

        } catch(PDOException $e){
            die($e->getMessage());
        }
    }

但是目前(使用下面的功能)，只有开发人员可以登录。如果您尝试使用设计师或雇主帐户登录，则会显示以下错误。有什么想法为什么会发生这种情况吗？

 if ($users->email_confirmed($email) === false) {
        $errors[] = 'Sorry, but you need to activate your account. Please check your emails.';
    } else // carry on logging in user

Answer 1

这是您的查询:

SELECT COUNT(developers.id)
FROM " . DB_NAME . ".developers
WHERE developers.email=  ? AND developers.confirmed = ? 
UNION
SELECT COUNT(designers.id)
FROM " . DB_NAME . ".designers
WHERE designers.email = ? AND designers.confirmed = ? 
UNION
SELECT COUNT(employers.id)
FROM " . DB_NAME . ".employers
WHERE employers.email = ? AND employers.confirmed = ?

它获取三行，然后删除重复项，并对它们进行任意排序。然后，您将从这些行之一读取值(计数)。

采用您正在采取的方法，我认为您想要:

select COUNT(*)
from ((select 1
       from " . DB_NAME . ".developers
       where developers.email=  ? AND developers.confirmed = ? 
      ) union all 
      (select 1
       from  " . DB_NAME . ".designers
       where designers.email = ? AND designers.confirmed = ? 
      ) union all
      (select 1
       from " . DB_NAME . ".employers
       where employers.email = ? AND employers.confirmed = ?
      )
     ) t