2014-07-18 23:59


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."%";                                

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%";

It also works If I just execute this :

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


  • dongwei1954 dongwei1954 7年前

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


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


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

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

    foreach ($Keywords as $n=>$Keyword) {                               

    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 ? ";
                   ^                 ^
    点赞 评论 复制链接分享