php - 在具有不同列的 3 个不同表中搜索用户

Question

我对此感到非常痛苦。我有 3 个用户表。它们位于不同的表中，因为根据用户的类型，它们具有不同的列。

现在要登录该网站，我尝试选择用户的电子邮件以及与他相对应的所有其他值。根据这里其他用户的一些反馈，我编写了如下查询:

    $query = "SELECT id, position_id, first_name, last_name, email ";
    $query .= "FROM pims ";
    $query .= "WHERE email = '{$email}' ";
    $query .= "AND hashed_password = '{$hashed_password}' ";
    $query .= "AND com_code IS NULL ";
    $query .= "LIMIT 1 ";

    $query .= "UNION ALL SELECT id, district_id, position_id, first_name, last_name, email ";
    $query .= "FROM dms ";
    $query .= "WHERE email = '{$email}' ";
    $query .= "AND hashed_password = '{$hashed_password}' ";
    $query .= "AND com_code IS NULL ";
    $query .= "LIMIT 1 ";

    $query .= "UNION ALL SELECT * ";
    $query .= "FROM users ";
    $query .= "WHERE email = '{$email}' ";
    $query .= "AND hashed_password = '{$hashed_password}' ";
    $query .= "AND com_code IS NULL ";
    $query .= "LIMIT 1";

但我收到“数据库查询失败”消息。当我只有一个 SELECT 语句时，查询工作正常，所以我知道是这个查询不起作用。有什么解决办法吗？

Answer 1

执行 union all 后，所有查询中的列数应相同。

$query  = "SELECT id, 'garbage_data' AS district_id, position_id, first_name, last_name, email ";
$query .= "FROM pims ";
$query .= "WHERE email = '{$email}' ";
$query .= "AND hashed_password = '{$hashed_password}' ";
$query .= "AND com_code IS NULL ";
$query .= "LIMIT 1 ";

$query .= "UNION ALL ";

$query .= "SELECT id, district_id, position_id, first_name, last_name, email ";
$query .= "FROM dms ";
$query .= "WHERE email = '{$email}' ";
$query .= "AND hashed_password = '{$hashed_password}' ";
$query .= "AND com_code IS NULL ";
$query .= "LIMIT 1 ";

$query .= "UNION ALL ";

$query .= "SELECT id, district_id, position_id, first_name, last_name, email ";
$query .= "FROM users ";
$query .= "WHERE email = '{$email}' ";
$query .= "AND hashed_password = '{$hashed_password}' ";
$query .= "AND com_code IS NULL ";
$query .= "LIMIT 1";

如果您缺少一列。您可以添加不存在的数据，例如:

'garbage_data' AS district_id

这将显示一个字符串garbage_data，它只是为了使列计数相同。

它可以是任何东西，甚至可以是'' AS District_id。