dsa1234569
dsa1234569
2014-07-18 23:59

如何使用数组中的多个关键字在mysql表中搜索列?

I am trying to make a search function for my database that refines the search with each additional keyword.

I don't want to limit it to a fixed number of keywords so I use a foreach() loop to extend the sql by another AND column LIKE ? for each keyword.

However the array-binder loop produces different results than using other methods that should do the exact same thing.

My table contains 1 column and 3 rows : "a b c", "b c", "1 2 3".

$Keywords = array("a","b","c"); //those search keywords should only apply to the first row
$sql = "SELECT * FROM table WHERE column LIKE ?";

foreach ($Keywords as $Keyword) {

    if ($count > 0) {       
        $sql = $sql . "AND column LIKE ?";
    }
    $count = $count + 1;
}
$query = $con->prepare($sql);

//loop to bind parameters
foreach ($Keywords as $n=>$Keyword) {
    $Keyword = "%".$Keyword."%";                                
    $query->bindParam($n+1,$Keyword,PDO::PARAM_STR);
}
$query->execute;

This will return the rows a b c and b c although it should only return the row that contains all 3 strings: a , b and c.

If I don't use a loop to bind the parameters (which limits the number of keywords) it does what is is supposed to and only shows "a b c".

        $a = "%a%";
        $b = "%b%";
        $c = "%c%";
        $query->bindParam(1,$a,PDO::PARAM_STR);
        $query->bindParam(2,$b,PDO::PARAM_STR);
        $query->bindParam(3,$c,PDO::PARAM_STR);

It also works If I just execute this :

$sql = "SELECT * FROM table WHERE column LIKE '%a%' AND column LIKE '%b%' AND column LIKE '%c%'";
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongwei1954 dongwei1954 7年前

    The working examples use the % wildcard but your looped values don't.

    Change

    $Keywords = array("a","b","c");
    

    to

    $Keywords = array("%a%","%b%","%c%");
    

    or another solution is to add the wildcards in the bindParam call

    foreach ($Keywords as $n=>$Keyword) {                               
       $query->bindParam($n+1,"%$Keyword%",PDO::PARAM_STR);
    }
    

    Another potential issue is that your constructed query doesn't have a space between ? and AND i.e.

    SELECT * FROM table WHERE column LIKE ?AND column LIKE ?AND column LIKE ?
    

    Although I'm not sure if a missing space after the ? is illegal, I recommend padding your AND condition with spaces

    $sql = $sql . " AND column LIKE ? ";
                   ^                 ^
    
    点赞 评论 复制链接分享