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
– CodeBird, Commented Apr 7, 2014 at 17:45
it might be the case the query function is sanitizing your query string — user1231969
– user1231969, Commented Apr 7, 2014 at 17:46

Marc B · Accepted Answer · 2014-04-07 19:55:27Z

3

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

edited Apr 7, 2014 at 19:55

answered Apr 7, 2014 at 19:48

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parag Tyagi · Accepted Answer · 2014-04-07 17:58:16Z

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 7, 2014 at 17:58

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

Comments

Ronnie Goodrich · Accepted Answer · 2014-04-07 20:03:55Z

0

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')

answered Apr 7, 2014 at 20:03

Ronnie Goodrich

411 silver badge8 bronze badges

Collectives™ on Stack Overflow

PHP with SQL wildcard search

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related