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

Question

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

Answer 1

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 ? ";
               ^                 ^