在数据库中搜索精确单词（不区分大小写）

Question

我有一个查询语句，用于获取指定记录的位置。

$sq = "SELECT COUNT(*) 
         FROM items LEFT JOIN info ON items.refID = info.refID 
           WHERE info.description = :status AND items.itmName < :itm 
             ORDER BY items.itmName";
$stmt = $connect->prepare($sq);
$stmt->execute(array(':status'=>"inStock", ':itm'=>$itm));
$rslt = $stmt->fetch(PDO::FETCH_NUM);
$pos = $rslt[0]+1;

问题（附带示例）： $itm = "Mango"

我在数据库中有一个名为 Mango 的项目，当我进行搜索时，它会返回 Mango 的正确位置 321 //运行良好 $itm = "Mangox"

如果我输入 Mangox 并进行搜索（Mangox 不存在），它仍然返回位置 321。

如何使搜索词与数据库上的确切项目匹配（但不区分大小写）？

- Becky

---

如果我没错的话...将items.itmName < :itm更改为items.itmName = :itm - user1844933

@user1844933 谢谢。如果我这样做，它不会对记录进行排序。 - Becky

你的代码中 :a 是否应该改成 :itm，以便与以下语句匹配：...WHERE info.description = :status AND items.itmName < :itm...？ - Sean

@Sean 谢谢。那是一个打字错误。我已经在我的帖子中修复了它。但这并没有解决问题。 - Becky

@Sean，我刚试了一下，不行。谢谢 :) - Becky

Answer 1

问题在于，为了获取“位置”，您正在计算所有名称中小于您要查找的名称的数量。

因此，无论您搜索什么，只要有比它小的名称，它就会返回大于0的数字。

例如，$itm =“ZZZ”可能会让您获得表中的最后一个位置。

另一个问题是，随后您执行以下操作：

$pos = $rslt[0]+1;

所以即使查询返回0，您仍将认为它是第1个位置。

从这个问题中得出的一个可能性（我没有运行它，所以它可能有一些语法问题，但是一般思路可能有效）：

SELECT rank
    FROM (
        SELECT @rn:=@rn+1 AS rank, itmName
        FROM (
            SELECT items.itmName
            FROM items LEFT JOIN info ON items.refID = info.refID 
            WHERE info.description = :status AND items.itmName <= :itm 
            ORDER BY items.itmName
        ) t1, (SELECT @rn:=0) t2
    )
WHERE itmName = :itm

无论如何，您都需要处理这样一个事实，即当名称不存在时，查询将不会返回任何数字。

- eugenioy

---

谢谢。但是$pos = $rslt[0];并不能解决这个问题。 - Becky

不会的。我并没有说会这样。不过，我正在编辑我的答案，包括一个可能的解决方案。 - eugenioy

这是一个用于跟踪行号的变量。更多关于变量的信息请参见：http://dev.mysql.com/doc/refman/5.0/en/user-variables.html - eugenioy

我刚刚尝试了你的解决方案，但是不行，谢谢。 - Becky

Answer 2

当列表中没有找到 Mangox 时，您希望返回什么结果？您想让查询不返回行吗？

由于查询只返回一行，因此不需要使用 ORDER BY 子句。在 WHERE 子句中的谓词否定了 LEFT JOIN 的外部性。（如果 info.description = something，那么意味着 info.description IS NOT NULL，这意味着它将排除任何来自 items 的行，这些行没有与 info 匹配的行。因此，它实际上是一个内连接。）

要在 items 表中不存在指定的 :itm 时返回无行，可以执行与返回零行的查询的连接操作。

  SELECT p.pos
    FROM ( SELECT COUNT(*) AS pos
             FROM items
             JOIN info
               ON items.refID = info.refID
            WHERE info.description = :status
              AND items.itmName < :itm
         ) p
   CROSS
    JOIN (SELECT 1
            FROM items r
           WHERE r.itmName = :itm2   -- additional bind placeholder
           LIMIT 1
         ) q

你需要再次传入$itm的值，用于添加绑定占位符。

  $stmt->execute(array(':status'=>"inStock", ':itm'=>$itm, ':itm2'=>$itm));
  //                                                     ^^^^^^^^^^^^^^^

当没有行返回时，fetch将返回FALSE，因此您需要在引用$rslt[0]之前进行额外的检查（确保您实际上获得了一行）。

  $rslt = $stmt->fetch(PDO::FETCH_NUM);
  if (!$rslt) {
     // row returned
     $pos = $rslt[0]+1;
  } else {
     // fetch failed, no row returned
     // ??? do whatever you want to do when the exact word is not matched
     $pos = 0; // ???
  }

编辑

即使您传递相同的值，使用不同的绑定占位符名称：itm2也有其特殊原因。这是因为，在语句中多次使用相同的绑定占位符名称（在PDO中）并不能像其他数据库接口那样按我们预期的方式工作。

- spencer7593