时间戳

首页 > 解决方案 > 使用 count(*) 搜索多个表的查询？

php - 使用 count(*) 搜索多个表的查询？

问题描述

我有一个在线商店的搜索字段，它使用 MySQL 查询 2 个不同的表。第一张表是乙烯基，有 13 列，第二张表是产品，有 10 列。我想要的结果是，如果您输入关键字，它将搜索表乙烯基的专辑名称和艺术家名称列，以及表产品中的名称列以查找任何匹配项。

我的 php 代码首先计算命中的次数，如果是，则再次使用查询来显示命中，就像在线商店一样。然而，当我测试它时，它只显示乙烯基表而不是产品表的结果。我尝试过使用UNION，但是因为两个表的列数不同，所以会导致错误。我已经使用NULL来填充联合所需的缺失列，但它也不起作用。连接也无济于事，因为这两个表没有任何相似的列。复杂之处在于第一个查询使用 count(*)。

使用 UNION 和 COUNT 的我的 SQL 查询：

(SELECT COUNT(*) AS numrows 
FROM vinyls 
WHERE (     album_name LIKE :keyword 
        OR  artist_name LIKE :keyword)
      ) 
union 
(SELECT COUNT(*) AS numrows 
FROM products 
WHERE name LIKE :keyword)

我的搜索字段的 php 代码：

$stmt = $conn->prepare("(SELECT COUNT(*) AS numrows 
                        FROM vinyls 
                        WHERE (     album_name LIKE :keyword 
                                OR  artist_name LIKE :keyword)
                              ) 
                        union 
                        (SELECT COUNT(*) AS numrows 
                        FROM products 
                        WHERE name LIKE :keyword)");

$stmt->execute(['keyword' => '%'.$_POST['keyword'].'%']);
$row = $stmt->fetch();


if($row['numrows'] < 1){
    echo '<h1 class="page-header">No results found for <i>'.$_POST['keyword'].'</i></h1>';
}else{
    echo '<h1 class="page-header">Search results for <i>'.$_POST['keyword'].'</i></h1>';
    try{
        $inc = 3;   
        $stmt = $conn->prepare("(SELECT * 
                                FROM vinyls 
                                WHERE (     album_name LIKE :keyword 
                                        OR  artist_name LIKE :keyword)
                                    ) 
                                union 
                                (SELECT * 
                                FROM products 
                                WHERE name LIKE :keyword)");

        $stmt->execute(['keyword' => '%'.$_POST['keyword'].'%']);

        foreach ($stmt as $row) {...

我已经不知道如何仅从一个搜索字段中搜索 2 个表并显示两个表的结果，而不仅仅是一个表。将商品添加到购物车时也是一个类似的问题，因为它们可以来自两个表，我必须再次“加入”这两个表。

我哪里做错了？

编辑：示例数据 Vinyls table +----+------------------+---------------------------+-----------+ | id | album_name | artist_name | genre | +----+------------------+---------------------------+-----------+ | 11 | Ravel Bolero | Boston Symphony Orchestra | Classical | | 12 | TV Calendar Show | Arthur Godfrey | Orchestra | | 13 | Flip Phillips | The Phillips Quartet | Jazz | | 14 | The Modern Idiom | Various artists | Jazz | +----+------------------+---------------------------+-----------+

Products table +----+-------------+------------------------------------------------------+ | id | category_id | name | +----+-------------+------------------------------------------------------+ | 31 | 15 | Tonka/Diecast trucks and cars | | 32 | 21 | Plate Number Georgia PQQ 1151 | | 33 | 6 | Walt Disney Read Along Casette Tapes | | 34 | 7 | Herbert Von Karajan | | 35 | 8 | BANK IT! Board Game | | 36 | 9 | Iraqi Most Wanted Playing Cards | | 37 | 10 | STAR TREK ( 1991,'92,'93 ) Stamp Oasis Rubber Stamps | | 38 | 11 | Batman and Friends Action Figure Toys (4" - 5") | | 39 | 12 | Delta Dawn Porcelain Doll | +----+-------------+------------------------------------------------------+

预期结果类似于亚马逊的搜索栏，您在其中键入关键字并在输入后显示结果。我希望在我的网站中，如果我在搜索字段中输入 Orchestra，它将显示 Boston Symphony Orchestra 的行。或者如果我输入汽车，它会显示 tonka 行。

我得到的是它只显示乙烯基表而不是产品表，即使我输入汽车并且产品表中应该有一个命中。或者我收到一个错误，因为联合仅适用于具有相似列数的表。

标签： phpmysqlsearch

解决方案

您的代码中有两个问题。

首先，您的计数查询应该可以正常工作并且不会引发错误，但您只查看第一个结果行（您的乙烯基之一）。但是您的查询返回两行。（它将返回一行包含numrows第一个联合查询（乙烯基）的字段，以及一行包含numrows您的联合中第二个查询（产品）的字段。

您可以将其包装成 aSUM只返回一个结果行：

$stmt = $conn->prepare("SELECT SUM(numrows) AS numrows FROM
                        (SELECT COUNT(*) AS numrows 
                        FROM vinyls 
                        WHERE (     album_name LIKE :keyword 
                                OR  artist_name LIKE :keyword)
                              ) 
                        union 
                        (SELECT COUNT(*) AS numrows 
                        FROM products 
                        WHERE name LIKE :keyword)) AS sumQuery");

第二个问题是查询，它会加载您的结果并由于列数不匹配而引发异常。您必须定义应该返回两个联合查询中的每一个的哪些列，并且它们必须在两者上都匹配。所以fe这样做：

        $stmt = $conn->prepare("(SELECT id, album_name AS name, artist_name
                                FROM vinyls 
                                WHERE (     album_name LIKE :keyword 
                                        OR  artist_name LIKE :keyword)
                                    ) 
                                union 
                                (SELECT id, name, NULL AS artist_name
                                FROM products 
                                WHERE name LIKE :keyword)");

推荐阅读