在具有不同列的3个不同表中搜索用户 - Search for user in 3 different tables that have different columns

Search for user in 3 different tables that have different columns

本文关键字：用户 3个搜索 | 更新日期: 2023-09-27

我玩得很开心。我有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语句时，查询运行良好，所以我知道是这个查询不起作用。有什么办法吗？

进行联合操作时，所有查询中的列数应相同。

$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。

首先，联合查询中列的数量和类型必须相同。其次，我建议你对此进行查看，然后使用该视图以方便使用，因为我不认为这是你需要这样查询的唯一地方。

$query = "SELECT id, '' 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 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 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";