PHP with SQL wildcard search

Question

In PHP I have a function to return results for a search form.

function search($search)    {
global $database;
$search2 = ( '%' . $search[0] . $search[1] . $search[2] . $search[3] . '%' );
$q1 = "SELECT * FROM location WHERE is_approved = 1 
AND (city LIKE '$search' 
OR     state LIKE '$search' 
OR state LIKE '$search2' 
OR 'city' LIKE '$search2')";

$matches = $database ->query($q1);
return $matches;
}

The search2 variable is meant to wildcard search for four character matches in the location such as 'Cinc' matching 'Cincinnati'. To do this the query needs a wildcard with the % character before and after.

Whenever I run the query directly in MYSQL to test it, it works fine. Whenever I run this in the PHP code the wildcard portion doesn't seem to be working. If I echo %search2 as in my example, $search2 will echo %Cinc% which is what I presume it should be for a wildcard search. Why are no results returned for the wildcard portion?

where are you fetching your query? like is there a fetchAll in `query` function? — CodeBird, Apr 07 '14 at 17:45
it might be the case the `query` function is sanitizing your query string — , Apr 07 '14 at 17:46

Marc B · Accepted Answer · 2014-04-07T19:55:27.673

You're literally generating an SQL search string that looks like

WHERE something LIKE '%foobarbaz%'

This will ONLY match fields where the field literally contains the exact string foobarbaz somewhere within it. If you want to search for the individual words foo, bar, and baz, you need

WHERE something like '%foo%' OR something like '%bar%' OR something LIKE '%baz%'

For multiple words, this gets very ugly very fast, and using %..% wildcards also prevents the use of indexes, meaning your query is going to be forcing a full table scan every time it executes.

You should probably look into using a FULLTEXT index instead, which would reduce your query too

WHERE MATCH (something) AGAINST ('foo bar baz')

WHERE

score 0 · Answer 2 · answered Apr 07 '14 at 17:58

0

Try this -

$search2 = '%'.$search[0],$search[1],$search[2],$search[3].'%';
$q1 = "SELECT * FROM location WHERE is_approved = 1 
AND (city LIKE '".$search."'
OR   state LIKE '".$search."' 
OR   state LIKE '".$search2."'
OR   city LIKE '".$search2."')";

answered Apr 07 '14 at 17:58

Parag Tyagi

8,780
3
42
47

score 0 · Answer 3 · answered Apr 07 '14 at 20:03

Syntax highlighting helped me spot it and I removed the single quote from 'city' in the query and it worked perfectly. It might be PHP parsing the single quote.

The original query

SELECT * FROM tournaments 
WHERE is_approved = 1 
AND (city LIKE '$search' 
OR state LIKE '$search' 
OR state LIKE '$search2' 
OR 'city' LIKE '$search2')

as opposed to

SELECT * FROM tournaments 
WHERE is_approved = 1 
AND (city LIKE '$search' 
OR state LIKE '$search' 
OR state LIKE '$search2' 
OR city LIKE '$search2')

PHP with SQL wildcard search

3 Answers3