How to Use SQL SELECT WHERE LIKE with a string that has spaces?

Question

When I do, let's say for example, a search of 'hello' I get all instances where 'hello' is used. But if I search 'hello my' I only get instances where those two words follow each other.

$search = 'hello my';
$search = explode(' ', $search);

Once I've exploded it I'm not sure what to do, I've tried using a for loop like so:

for($i = 0; $i < count($search); $i++) {
    $search = $search[$i];
}

And I've also tried putting my SQL SELECT inside the for loop so it does multiple searches but that hasn't worked. My SQL syntax looks like this:

SELECT * FROM table WHERE string LIKE '%$search%';

if the search term is `hello my` what do you want to match? – Nov 25 '18 at 02:47 — , Nov 25 '18 at 02:47

score 3 · Accepted Answer · answered Nov 25 '18 at 02:47

You could try something like this to search for any of the words in your search string:

$search = 'hello my';
$search = explode(' ', $search);
$query = "SELECT * FROM table WHERE string LIKE '%" . implode("%' OR string LIKE '%", $search) . "%'";
echo $query;

Output:

SELECT * FROM table WHERE string LIKE '%hello%' OR string LIKE '%my%'

If you want to search for all words in the search string, change the OR in the above code to AND i.e.

$query = "SELECT * FROM table WHERE string LIKE '%" . implode("%' AND string LIKE '%", $search) . "%'";
echo $query;

Output:

SELECT * FROM table WHERE string LIKE '%hello%' AND string LIKE '%my%'

Demo on 3v4l.org

How to Use SQL SELECT WHERE LIKE with a string that has spaces?

1 Answers1