使用PDO准备好的语句从搜索字段中使用多个关键字进行LIKE查询 [英] LIKE query using multiple keywords from search field using PDO prepared statement

问题描述

站点用户使用搜索表单来查询产品数据库.输入的关键字在数据库中搜索产品的标题.

Site users use a search form to query a database of products. The keywords entered search the titles for the products in the database.

    public function startSearch($keywords){
        $keywords = preg_split('/[\s]+/', $keywords);
        $totalKeywords = count($keywords);

        foreach($keywords as $key => $keyword){
            $search .= '%'.$keyword.'%';
            if($key != ($totalKeywords)-1){
                $search .= ' AND itemTitle LIKE ';
            }
        }
$sql=$this->db->prepare("SELECT * FROM prodsTable WHERE itemTitle LIKE ?");
$sql->bindParam(1, $search);        
$sql->execute ();
$sql->fetchALL(PDO::FETCH_ASSOC);

如果用户输入单个关键字，则搜索有效，但是如果使用多个关键字，则查询不会执行.

The search works if a user enters a single keyword, but if multiple keywords are used the query does not execute.

如果: $ keywords ='苹果ipod'; $ search ='％apple％AND itemTitle Like％ipod％';

if: $keywords = 'apple ipod'; $search = '%apple% AND itemTitle LIKE %ipod%';

因此，准备好的语句应如下所示:

So the prepared statement should look like this:

从prodsTable中选择*的itemTitle Like％apple％和itemTitle Like％ipod％"

"SELECT * FROM prodsTable WHERE itemTitle LIKE %apple% AND itemTitle LIKE %ipod%"

如果两个产品的标题中同时包含"apple"和"ipod"，则没有结果返回.

No results return when two products should return having both "apple" and "ipod" in their titles.

我在做什么错了?

推荐答案

准备好的语句可以防止SQL注入，因此不会解释参数中的sql代码.在调用prepare()之前，您将必须使用正确的AND itemTitle LIKE ?编号构建sql查询.

Prepared statements protect you from sql injection, so sql code in the parameters will not be interpreted. You will have to build a sql query with the correct number of AND itemTitle LIKE ? before calling prepare().

  $keywords = preg_split('/[\s]+/', $keywords);
  $totalKeywords = count($keywords);
  $query = "SELECT * FROM prodsTable WHERE itemTitle LIKE ?";

  for($i=1 ; $i < $totalKeywords; $i++){
    $query .= " AND itemTitle LIKE ? ";
  }

  $sql=$this->db->prepare($query);
  foreach($keywords as $key => $keyword){
    $sql->bindParam($key+1, '%'.$keyword.'%');
  }
  $sql->execute ();

这篇关于使用PDO准备好的语句从搜索字段中使用多个关键字进行LIKE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！