php - MySQL PDO UNION 查询，查找结果来自的表

Question

这个问题已经有答案了:

奥 git _a (4 个回答)

已关闭 6 年前。

我正在尝试创建一个查询，该查询当前跨越三个表来查找各种 ID，但我需要知道 ID 来自哪个表。

$searchQuery = 'LS7 2UP';

$stmt = $db->prepare("
SELECT id as 'nameSearchID' FROM user WHERE recordStatus=1 AND CONCAT( forename,  ' ', surname ) LIKE :nameSearch
UNION ALL
SELECT clientID AS 'salesOrderClientID' FROM salesOrder WHERE recordStatus=1 AND salesOrderID LIKE :salesOrderID
UNION ALL
SELECT id AS 'addressID' FROM address WHERE recordStatus=1 AND postcode LIKE :searchPostcode
");

$stmt->bindValue(':nameSearch', "%$searchQuery%", PDO::PARAM_STR);
$stmt->bindValue(':salesOrderID', "%$searchQuery%", PDO::PARAM_STR);
$stmt->bindValue(':searchPostcode', "%$searchQuery%", PDO::PARAM_STR);

$stmt->execute();
$rowCount = $stmt->rowCount();
$resultsOrig = $stmt->fetchAll(PDO::FETCH_ASSOC);

这会输出 2 个从地址表中找到的结果，但它显示为来自“用户”表。

有什么想法吗？

Array
(
    [0] => Array
        (
            [nameSearchID] => 69e17996-93be-4562-ad55-49ac62801bba
        )

    [1] => Array
        (
            [nameSearchID] => 135e4e26-869d-44cc-b400-383e884923ea
        )

)

Answer 1

将其添加为参数:

SELECT id as 'nameSearchID', "user" FROM user WHERE recordStatus=1 AND CONCAT( forename,  ' ', surname ) LIKE :nameSearch
UNION ALL
SELECT clientID AS 'salesOrderClientID', "salesOrder" FROM salesOrder WHERE recordStatus=1 AND salesOrderID LIKE :salesOrderID
UNION ALL
SELECT id AS 'addressID', "address" FROM address WHERE recordStatus=1 AND postcode LIKE :searchPostcode
"