php - 从具有多个表的查询中获取pdo表信息

$lister_query = "
  SELECT * FROM (SELECT
    lister1_password lister_password,
    lister1_status   lister_status,
    lister1_email    lister_email
  FROM lister1
  UNION
  SELECT
    lister2_password lister_password,
    lister2_status   lister_status,
    lister2_email    lister_email
  FROM lister2
  UNION
  SELECT
    lister3_password lister_password,
    lister3_status   lister_status,
    lister3_email    lister_email
  FROM lister3) AS lister
  WHERE lister.lister_password = ? AND lister.lister_email = ?
";

$lister_prepare = $database_listers -> prepare($lister_query);
$lister_prepare                     -> execute($_EXECUTE);

$_LISTER_COUNT = $lister_prepare -> rowCount();

if ($_LISTER_COUNT === 1) {
  $_LISTER = $lister_prepare -> fetch();

  $lister_status = $_LISTER['lister_status'];
  $lister_key    = $_LISTER['lister_key'];
}

如何找到找到电子邮件和密码集的表。我需要知道找到的是lister1、lister2还是lister3。这可能吗？
解决方案（当前不工作）

$lister_query = "
      SELECT * FROM (SELECT
        lister1_password lister_password,
        lister1_status   lister_status,
        lister1_email    lister_email,
        'test1' test
      FROM lister1
      UNION
      SELECT
        lister2_password lister_password,
        lister2_status   lister_status,
        lister2_email    lister_email,
        'test2' test
      FROM lister2
      UNION
      SELECT
        lister3_password lister_password,
        lister3_status   lister_status,
        lister3_email    lister_email,
        'test3' test
      FROM lister3) AS lister
      WHERE lister.lister_password = ? AND lister.lister_email = ?
    ";

    $lister_prepare = $database_listers -> prepare($lister_query);
    $lister_prepare                     -> execute($_EXECUTE);

    $_LISTER_COUNT = $lister_prepare -> rowCount();

    if ($_LISTER_COUNT === 1) {
      $_LISTER = $lister_prepare -> fetch();

     echo $_LISTER['test'];

      $lister_status = $_LISTER['lister_status'];
      $lister_key    = $_LISTER['lister_key'];
    }

我补充了讨论的解决方案。它没有回显测试变量，而其他获取的变量现在也不再工作。

最佳答案

您可以在定义表的每个UNION SELECT中添加另一列

$lister_query = "
  SELECT * FROM (SELECT
    lister1_password lister_password,
    lister1_status   lister_status,
    lister1_email    lister_email,
    'lister1'        lister_table  // define lister_table
  FROM lister1
  UNION
  SELECT
    lister2_password lister_password,
    lister2_status   lister_status,
    lister2_email    lister_email,
    'lister2'        lister_table  // define lister_table
  FROM lister2
  UNION
  SELECT
    lister3_password lister_password,
    lister3_status   lister_status,
    lister3_email    lister_email,
    'lister3'        lister_table  // define lister_table
  FROM lister3) AS lister
  WHERE lister.lister_password = ? AND lister.lister_email = ?
";

...
[rest of your code]
...

if ($_LISTER_COUNT === 1) {
  $_LISTER = $lister_prepare -> fetch();

  $lister_table = $_LISTER['lister_table']; // the table it came from
  $lister_status = $_LISTER['lister_status'];
  $lister_key    = $_LISTER['lister_key'];
}

关于php - 从具有多个表的查询中获取pdo表信息，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30905488/