针对全文搜索的mysql匹配不能与php pdo bindparam动态工作

3hvapo4f 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(255)

我用jqueryui在我的网站上实现了一个ajax自动完成搜索，它做得很好，但是我遇到了一个select问题，它不想工作。
中的“自动完成搜索名称”字段 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 quote动态插入参数时，就像下面的示例一样，select不再起作用。我做了很多研究，但找不到解决这个问题的方法。

$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;

mysql php pdo

来源：https://stackoverflow.com/questions/50341011/mysql-match-against-full-text-search-not-working-dynamically-with-php-pdo-bindpa

1条答案

按热度按时间

nqwrtyyt1#

因为你有 :query 在单引号中，所以mysql将其视为字符串，而不是参数。
您可以通过替换 '*:query*' 与 :query 将绑定更改为：

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

或者你可以替换 '*:query*' 与 CONCAT('*', :query, '*') 您还可能遇到在一个查询中两次使用同一参数名的限制（请参阅手册）（只有在不使用模拟的prepared语句时才会发生这种情况）。在这种情况下，您需要按以下方式更改代码：

$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();

赞(0）回复(0）举报 2021-06-21

我来回答

针对全文搜索的mysql匹配不能与php pdo bindparam动态工作

1条答案

相关问题

热门标签

最新问答