Binding an unknown number of keywords to a SQL statement with wildcards

I want to bind keywords to a SQL query like this:

SELECT `field_1`, `field2` FROM `table` WHERE 
`field_1` LIKE '%keyword1%' 
OR `field_1` LIKE '%keyword2%'
OR `field_1` LIKE '%keyword3%'

Note that the number of keywords is NOT predetermined. It is in fact determined by an array which itself is determined by exploding user’s input on space.

I am using Laravel 7. So, I am looking for a solution that is consistent with Laravel 7 and PDO. The problem is that I can’t use '%?%' in my SQL statement because then I won’t be able to bind the values in my array to it.

I am sure there should be a solution for this as it seems like a common problem in writing simple search engines, but I can’t find it on my own.

Answer

Regarding your example query, you need to add the % to the value instead of the statement so it’s like this:

WHERE foo LIKE ? OR foo LIKE ?

And then pass the values as "%$var1%", "%$var2%" and so on.

Using Laravels Query Builder

Make sure you have all the search words in an array. Then you can, in Laravels (which you mentioned you’re using) query builder, do something like this:

$searchWords = ['val1', 'val2', 'val3'];

// Now get the query builder and pass closure as the argument
$query = DB::table('some-table')
    ->where(function($query) use ($searchWords) {
        // Iterate through the words and add them one by one
        foreach ($searchWords as $word) {
            $query->orWhere('field', 'LIKE', "%{$word}%");
        }
    });

$result = $query->get();