Searching Database PHP/MYSQL Question

Question

Right now I'm just using a simple

WHERE name LIKE '%$ser%'

But I'm running into an issue - say the search is Testing 123 and the "name" is Testing, it's not coming back with any results. Know any way to fix it? Am I doing something wrong?

Answer 1

That's how LIKE works - it returns rows that completely contain the search string, and, if you use "%" optionally contain something else.

If you want to see if the field is contained in a string, you can do it this way:

SELECT * FROM `Table` WHERE  "Testing 123" LIKE CONCAT("%",`name`,"%") 

Answer 2

If you want to search for 'Testing' or '123' use OR:

WHERE (name LIKE '%Testing%' OR name LIKE '%123%')

Note however that this will be very slow as no index can be used and it may return some results you didn't want (like "4123"). Depending on your needs, using a full text search or an external database indexing product like Lucene might be a better option.

Answer 3

After the variable $ser is replaced, the query is:

WHERE name LIKE '%Testing 123%'

You should build the query separating by words:

WHERE name LIKE '%$word[1]%$word[2]%'

Answer 4

As Scott mentioned, you cannot check to see if the search contains the column value, it works the other way round. so if $ser = "testing" and table has a row name = testing 123 it will return

For what you're trying to do you'll need to tokenize the search query into terms and perform an OR search with each of them or better still check out mysql full text search for a much better approach

Answer 5

not efficient (as your example) but working as you want:

WHERE name LIKE '%$ser%' OR '$ser' LIKE CONCAT('%', name, '%')

Answer 6

As mentioned by Mark and others, a full text search method may be better if possible.

However, you can split the search string on word boundary and use OR logic—but check for the whole string first, then offer the option to widen the search:

NOTE: Input sanitization and preparation not shown.

1. Query with:

$sql_where = "WHERE name LIKE '%$ser%'";  

2. If zero results are returned, ask user if they would like to query each word individually.

3. If user requests an 'each word' search, query with:

$sql_where = get_sql_where($ser);

(Working) Example Code Below:

$ser = 'Testing 123';
$msg = '';

function get_sql_where($ser){
    global $msg;
    $sql_where = '';
    $sql_where_or = '';

    $ser = preg_replace("/[[:blank:]]+/"," ", trim($ser)); //replace consecutive spaces with single space
    $search_words = explode(" ", $ser);

    if($search_words[0] == ''){
        $msg = 'Search quested was blank.';
    }else{
        $msg = 'Search results for any of the following words:' . implode(', ', $search_words);
        $sql_where = "WHERE name LIKE '%$ser%'";
        foreach($search_words as $word){
            $sql_where_or .= " OR name LIKE '%$word%'";
        }
    }

    return $sql_where . $sql_where_or;
}

$sql_where = get_sql_where($ser);
//Run query using $sql_where string