mysql LIKE not working correctly with OR

Question

I have a search bar on my website in which i used google place api autocomplete service with the help of this user can insert city,state,country,address,zip code.i have fields in mysql database are country,state,city,address,zip code.i am using mysql LIKE clause to search records.My query is:

$sql = "SELECT * 
         FROM location
         WHERE ((country LIKE '%".$search."%') 
         OR (city LIKE '%".$search."%') 
         OR (state LIKE'%".$search."%') 
         OR (address LIKE'%".$search."%')
         OR (zipcode LIKE'%".$search."%'))";

I tried to remove comma from search string but no luck.if i insert only country or only city or only state or only address or only zipcode then it gives correct result but when i search like address,city,state,country or change the order it doesn't work please help me out.thanks in advance.

I think closing double quote is missing.. Also I assume you know LIKE is case sensitive.. — JanT, Sep 24 '15 at 13:22
Seems very much like this question earlier today : http://stackoverflow.com/questions/32757828/how-to-search-multiple-columns-in-database-with-one-searchbar — PaulF, Sep 24 '15 at 13:29
What does your `$search` string look like if it has say, `city` and `zipcode` in it? If it is one string then it will be unlikely to match anything. It would be easier, if you kept the input as separate fields, to match them with the `like` tests. i.e. have `$citySearch`, `$stateSearch` etc. — Ryan Vincent, Sep 24 '15 at 13:38
thanks for your reply johnyTee but i didn't miss double quetes. — arjun, Sep 24 '15 at 13:51

score 1 · Answer 1 · answered Sep 24 '15 at 13:26

Be careful at spaces and quotes. Try it like this:

$sql = "SELECT * 
          FROM location
         WHERE country LIKE '%$search%' 
            OR city    LIKE '%$search%' 
            OR state   LIKE '%$search%' 
            OR address LIKE '%$search%'
            OR zipcode LIKE '%$search%'";

score 0 · Answer 2 · answered Sep 24 '15 at 13:31

0

You are trying to get records with LIKE statement, try to concatenate the fields like this:

$sql = "SELECT * FROM location WHERE CONCAT(country, ' ', state, ' ',city, ' ', address, ' ', zip_code) like '%" . $search . "%'";

If you want better results, you can use PHP function explode like this:

    $words = explode(' ', $search);
$i = 0;
foreach($words as $p) {
    if ($i == 0) {
        $sql_aux = $sql_aux . "CONCAT(country, ' ', state, ' ',city, ' ', address, ' ', zip_code) like '%" . $p . "%'";
    }
    elseif($i > 0) {
        $sql_aux = $sql_aux . "and CONCAT(country, ' ', state, ' ',city, ' ', address, ' ', zip_code) like '%" . $p . "%'";
    }
    $i++;
}

answered Sep 24 '15 at 13:31

nun3z

108
2
8

Thanks for reply but it's not working for me.i search like Port Barrington,IL,United States.it give me those records which addresses are match same but those who dont have same address are not come.but my concern is those records who belongs to IL or United States are also come. – arjun Sep 24 '15 at 14:09
would it work if you replace the comma characters to space characters from the string $search before you use it on the SQL statement? – nun3z Sep 24 '15 at 14:14

score 0 · Answer 3 · edited May 23 '17 at 12:29

This is not problem you will be able to solve simply in SQL.

Important side note: read up on SQL injection. Never, under any circumstances, should you build a query by concatenating variables directly into it.

What you are trying to do -- if you stop and look at it from the database's perspective -- is ask the database to read every row of your table and search for possible matches.

Even if it works, it isn't going to work well, unless you have an extremely small dataset, because the entire table has to be read for each query.

Your comparison, though, is backwards.

You probably don't really want to evaluate this:

city LIKE '%$string%'
state LIKE '%$string%'

'Houston' LIKE '%Houston, TX%' # false!
'TX' LIKE '%Houston, TX%' # also false!

When you consider what your query is asking the server to do, it seems obvious that your logic does not work. The arguments to LIKE are not in any sense commutative.

What you are actually trying to find is more like this:

'$string' LIKE CONCAT('%',city,'%')
'$string' LIKE CONCAT('%',state,'%')

'Houston, TX' LIKE '%Houston%' # true!
'Houston, TX' LIKE '%TX%' # also true!

You actually need to reverse the comparisons to make the query logically correct.

However, logically correct is still going to perform terribly for larger datasets, because so much work has to be done by the server and none of the shortcuts often available to the query optimizer can be used. This is a brute force approach, essentially the worst approach possible.

The correct solution is far more complicated: You have to tokenize the address in the application and make an intelligent query, which will be an entirely different query, depending on the patterns found in the content of the search box. Once you identify what you believe to be, for example, a state, your WHERE clause should be specific, and left-anchored:

WHERE state LIKE CONCAT($parsed_state,'%')

Never use a % at the beginning of a LIKE argument if it can be avoided. Use them only at the end, if you want to have reasonable performance and potentially be able to take advantage of an index on the column.

Tokenizing free-form addresses, even "complete" ones, is no small feat. See, for example, How to parse freeform street/postal address out of text, and into components

mysql LIKE not working correctly with OR

3 Answers3