php - MySQL MATCH AGAINST FULL TEXT SEARCH 不能与 php PDO bindParam 一起动态工作

Question

我使用 jQuery UI 在我的网站上实现了一个 ajax 自动完成搜索，它做得很好，但是我遇到了一个不想工作的选择问题。

中的 autocomplete_search_name 字段database已经是FULLTEXT index.

以下select在静态模式下工作正常，这是我希望搜索工作的方式，带有**

    $stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST('*psico*' IN BOOLEAN MODE) ORDER BY MATCH(autocomplete_search_name) AGAINST('*psico*') DESC LIMIT 10");
    $stmt->execute();
    $moddados = $stmt->fetchAll();
    return $moddados;

但是，当我开始使用 PDO bindParam 或 PDO 引用动态插入参数时，如下例所示，选择不再起作用。我研究了很多，但找不到解决这个问题的方法。

    $data['query'] = '*'.$data['query'].'*';
    $stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(:query IN BOOLEAN MODE) ORDER BY MATCH(autocomplete_search_name) AGAINST(:query) DESC LIMIT 10");
    $stmt->bindParam(':query', $data['query']);
    $stmt->execute();
    $moddados = $stmt->fetchAll();
    return $moddados;

我在下面尝试过这种方式，但它也不起作用：

$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query, '*') IN BOOLEAN MODE) ORDER BY MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query, '*')) DESC LIMIT 10");
    $stmt->bindParam(':query', $data['query']);
    $stmt->execute();
    $moddados = $stmt->fetchAll();
    return $moddados;

而且这种方式也行不通

$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(':query' IN BOOLEAN MODE) ORDER BY MATCH(autocomplete_search_name) AGAINST(':query') DESC LIMIT 10");
    $stmt->bindParam(':query', '*' . $data['query'] . '*');
    $stmt->execute();
    $moddados = $stmt->fetchAll();
    return $moddados;

标签： phpmysqlpdo

Answer 1

这是因为您有:query单引号，因此 MySQL 将其视为字符串，而不是参数。

您可以通过替换和更改绑定来解决'*:query*'它:query：

$stmt->bindParam(':query', '*' . $data['query'] . '*');

或者你可以'*:query*'用CONCAT('*', :query, '*')

您可能还会遇到在一个查询中两次使用相同参数名称的限制（请参阅手册）（仅当您不使用模拟的准备好的语句时才会发生这种情况）。在这种情况下，您需要按如下方式更改代码：

$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product 
                            WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query1, '*') IN BOOLEAN MODE) 
                            ORDER BY MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query2, '*')) DESC 
                            LIMIT 10");
$stmt->bindParam(':query1', $data['query']);
$stmt->bindParam(':query2', $data['query']);
$stmt->execute();